34,591
社区成员
发帖
与我相关
我的任务
分享临时表查询，求好心的高手教教我
数据库
编号 字段1 字段2
1 AA|BB|CC 888|999|666
2 AA|BB|CC 111|222|333
3 AA|BB|CC 444|555|666
显示效果
编号 AA BB CC
1 888 999 666
2 111 222 333
3 444 555 666
怎么通过SQL语句查出下面的效果啊。网上的了下资料用临时表。求高手，在线等......
编号 字段1 字段2
1 AA|BB|CC 888|999|666
2 AA|BB|CC 111|222|333
3 AA|BB|CC 444|555|666
显示效果
编号 AA BB CC
1 888 999 666
2 111 222 333
3 444 555 666
怎么通过SQL语句查出下面的效果啊。网上的了下资料用临时表。求高手，在线等......
...全文
请发表友善的回复…
发表回复
我腫了 2012-05-30
- 打赏
- 举报
USE Test
go
--数据库
--编号 字段1 字段2
--1 AA|BB|CC 888|999|666
--2 AA|BB|CC 111|222|333
--3 AA|BB|CC 444|555|666
--
--
--
--
--显示效果
--编号 AA BB CC
--1 888 999 666
--2 111 222 333
--3 444 555 666
if object_id('t1') is not null
drop table t1
Go
Create table t1([编号] smallint,[字段1] nvarchar(8),[字段2] nvarchar(11))
Insert into t1
Select 1,N'AA|BB|CC',N'888|999|666'
Union all Select 2,N'AA|BB|CC',N'111|222|333'
Union all Select 3,N'AA|BB|CC',N'444|555|666'
DECLARE @Sql NVARCHAR(MAX)
IF object_id('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
;WITH c1 AS(
SELECT [编号],[字段1]+'|' AS [字段1],[字段2]+'|' AS [字段2] FROM t1
), c2 AS (
SELECT
编号
,CONVERT(NVARCHAR(MAX),LEFT(字段1,CHARINDEX('|',字段1)-1)) AS 字段1
,CONVERT(NVARCHAR(MAX),STUFF(字段1,1,CHARINDEX('|',字段1),'')) AS tmpCol
,1 AS Row
FROM c1
UNION ALL
SELECT
编号
,CONVERT(NVARCHAR(MAX),LEFT(tmpCol,CHARINDEX('|',tmpCol)-1)) AS 字段1
,CONVERT(NVARCHAR(MAX),STUFF(tmpCol,1,CHARINDEX('|',tmpCol),'')) AS tmpCol
,1+1 AS Row
FROM c2
WHERE CHARINDEX('|',tmpCol)>0
), c3 AS (
SELECT
编号
,CONVERT(NVARCHAR(MAX),LEFT(字段2,CHARINDEX('|',字段2)-1)) AS 字段2
,CONVERT(NVARCHAR(MAX),STUFF(字段2,1,CHARINDEX('|',字段2),'')) AS tmpCol
,1 AS Row
FROM c1
UNION ALL
SELECT
编号
,CONVERT(NVARCHAR(MAX),LEFT(tmpCol,CHARINDEX('|',tmpCol)-1)) AS 字段2
,CONVERT(NVARCHAR(MAX),STUFF(tmpCol,1,CHARINDEX('|',tmpCol),'')) AS tmpCol
,1+1 AS Row
FROM c3
WHERE CHARINDEX('|',tmpCol)>0
)
SELECT a.编号,a.字段1,b.字段2
INTO #tmp
FROM c2 AS a
INNER JOIN c3 AS b ON a.编号=b.编号 AND a.Row=b.Row
SELECT @Sql=ISNULL(@Sql+',','')+N'MAX(CASE WHEN 字段1=N'''+字段1+''' THEN 字段2 END) AS '+字段1 FROM #tmp
GROUP BY 字段1
EXEC ( N'SELECT 编号,'+@Sql+' FROM #tmp GROUP BY 编号' )
IF object_id('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
开着拖拉机泡妞 2012-05-20
- 打赏
- 举报
--> 测试数据：[数据库]
if object_id('[数据库]') is not null drop table [数据库]
create table [数据库](
[编号] int,
[字段1] varchar(8),
[字段2] varchar(11)
)
insert [数据库]
select 1,'AA|BB|CC','888|999|666' union all
select 2,'AA|BB|CC','111|222|333' union all
select 3,'AA|BB|CC','444|555|666'
SELECT * INTO #TEST FROM(
select [编号],
LEFT([字段1],CHARINDEX('|',[字段1])-1) AS [字段1A],
LEFT([字段2],CHARINDEX('|',[字段2])-1) AS [字段2A]
from [数据库]
UNION ALL
select [编号],
SUBSTRING([字段1],CHARINDEX('|',[字段1])+1,CHARINDEX('|',[字段1],CHARINDEX('|',[字段1]))-1) AS [字段1B],
SUBSTRING([字段2],CHARINDEX('|',[字段2])+1,CHARINDEX('|',[字段2],CHARINDEX('|',[字段2]))-1) AS [字段2B]
from [数据库]
UNION ALL
select [编号],
RIGHT([字段1],CHARINDEX('|',REVERSE([字段1]))-1) AS [字段1C],
RIGHT([字段2],CHARINDEX('|',REVERSE([字段2]))-1) AS [字段2C]
from [数据库]
)T
--如果数据格式是固定你给的那样的可以这样处理，然后再行列转换一下就好了
DECLARE @STR VARCHAR(2000)
SET @STR=''
SELECT @STR=@STR+','+[字段1A]+'=MAX(CASE WHEN [字段1A]='+QUOTENAME([字段1A],'''')+
' THEN [字段2A] END)' FROM #TEST GROUP BY [字段1A]
EXEC('SELECT [编号]'+@STR+' FROM #TEST GROUP BY [编号]')
/*
编号 AA BB CC
---------------------------
1 888 999 666
2 111 222 333
3 444 555 666
*/
额，上面字段名写错了
koren911 2012-05-20
- 打赏
- 举报
[Quote=引用 6 楼 的回复:]
SQL code
--> 测试数据：[数据库]
if object_id('[数据库]') is not null drop table [数据库]
create table [数据库](
[编号] int,
[字段1] varchar(8),
[字段2] varchar(11)
)
insert [数据库]
select 1,'AA|BB|CC','888|999|66……
[/Quote]
大侠
数据好像错了哦。怎么有 B|C啊
还有就是 我不一定做死是'AA|BB|CC' 可能有时会是'AA|BB|CC|DD|EE'
当然我一次只会有一种格式。是能过标识符过滤出来的
您能再帮我看看么
SQL code
--> 测试数据：[数据库]
if object_id('[数据库]') is not null drop table [数据库]
create table [数据库](
[编号] int,
[字段1] varchar(8),
[字段2] varchar(11)
)
insert [数据库]
select 1,'AA|BB|CC','888|999|66……
[/Quote]
大侠
数据好像错了哦。怎么有 B|C啊
还有就是 我不一定做死是'AA|BB|CC' 可能有时会是'AA|BB|CC|DD|EE'
当然我一次只会有一种格式。是能过标识符过滤出来的
您能再帮我看看么
开着拖拉机泡妞 2012-05-20
- 打赏
- 举报
--> 测试数据：[数据库]
if object_id('[数据库]') is not null drop table [数据库]
create table [数据库](
[编号] int,
[字段1] varchar(8),
[字段2] varchar(11)
)
insert [数据库]
select 1,'AA|BB|CC','888|999|666' union all
select 2,'AA|BB|CC','111|222|333' union all
select 3,'AA|BB|CC','444|555|666'
with T as
(
select [编号],charindex('|','|'+[字段1])col1a,
charindex('|',[字段1]+'|')+1 col1b,
charindex('|','|'+[字段2]) col2a,
charindex('|',[字段2]+'|')+1 col2b
from [数据库]
union all
select a.编号,b.col1b,charindex('|',[字段1]+'|',b.col1b)+1,
b.col2b,charindex('|',[字段2]+'|',b.col2b)+1
from [数据库] a join T b on a.编号=b.编号
where charindex('|',[字段1]+'|',b.col1b)>0
or charindex('|',[字段2]+'|',b.col2b)>0
)
select
a.编号,[字段1]=substring(a.[字段1]+'|',b.col1a,b.col1b - b.col1a - 1),
[字段2]=substring(a.[字段2]+'|',b.col2a,b.col2b - b.col2a - 1) into #test
from [数据库] a join T b on a.编号=b.编号
order by 1
declare @str varchar(2000)
set @str=''
select
@str=@str+','+[字段1]+'=max(case when [字段1]='
+quotename([字段1],'''')+' then [字段2] else 0 end)'
from
#test
group by
[字段1]
exec('select 编号'+@str+' from #test group by 编号')
/*
编号 AA BB CC
-------------------------
1 888 999 666
2 111 222 333
3 444 555 666
*/
--用这个方法吧
koren911 2012-05-19
- 打赏
- 举报
[Quote=引用 1 楼 的回复:]
SQL code
-- 建表
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb;
GO
CREATE TABLE tb
( id INT,
vt VARCHAR(500),
vt1 varchar(500)
);
GO
INSERT INTO tb(id, vt, vt1) VALUES(1,'AA|BB|CC'|'……
[/Quote]
在关键字 'with' 附近有语法错误。
大侠再帮我看看呢
SQL code
-- 建表
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb;
GO
CREATE TABLE tb
( id INT,
vt VARCHAR(500),
vt1 varchar(500)
);
GO
INSERT INTO tb(id, vt, vt1) VALUES(1,'AA|BB|CC'|'……
[/Quote]
在关键字 'with' 附近有语法错误。
大侠再帮我看看呢
koren911 2012-05-19
- 打赏
- 举报
[Quote=引用 2 楼 的回复:]
SQL code
create table test(id int,varN varchar(100),varD varchar(100))
insert into test
select 1 ,'AA|BB|CC' ,'888|999|666' union all
select 2 ,'AA|BB|CC' ,'111|222|333' union all
select 3 ,'……
[/Quote]
这样可以，但有一个问题 万一我后面的数据里有. 就会报错呢
SQL code
create table test(id int,varN varchar(100),varD varchar(100))
insert into test
select 1 ,'AA|BB|CC' ,'888|999|666' union all
select 2 ,'AA|BB|CC' ,'111|222|333' union all
select 3 ,'……
[/Quote]
这样可以，但有一个问题 万一我后面的数据里有. 就会报错呢
AcHerat 元老 2012-05-19
- 打赏
- 举报
create table test(id int,varN varchar(100),varD varchar(100))
insert into test
select 1 ,'AA|BB|CC' ,'888|999|666' union all
select 2 ,'AA|BB|CC' ,'111|222|333' union all
select 3 ,'AA|BB|CC' ,'444|555|666'
go
declare @sql varchar(4000)
select @sql = isnull(@sql+' union all ','')
+ 'select id,parsename(replace(varD,''|'',''.''),3) as '+parsename(replace(varN,'|','.'),3)
+ ',parsename(replace(varD,''|'',''.''),2) as '+parsename(replace(varN,'|','.'),2)
+ ',parsename(replace(varD,''|'',''.''),1) as '+parsename(replace(varN,'|','.'),1)
+ ' from test where id = ' +ltrim(id)
from test
exec(@sql)
drop table test
/************************
(3 行受影响)
id AA BB CC
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
1 888 999 666
2 111 222 333
3 444 555 666
(3 行受影响)
simonxt 2012-05-19
- 打赏
- 举报
-- 建表
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb;
GO
CREATE TABLE tb
( id INT,
vt VARCHAR(500),
vt1 varchar(500)
);
GO
INSERT INTO tb(id, vt, vt1) VALUES(1,'AA|BB|CC'|'888|999|666');
INSERT INTO tb(id, vt, vt1) VALUES(2,'AA|BB|CC'|'111|222|333');
INSERT INTO tb(id, vt, vt1) VALUES(3,'AA|BB|CC'|'444|555|666');
select * from tb;
-- 截字符，得到临时表
;with t as(
select
id,
vt=(case when charindex('|',vt)>0 then substring(vt, charindex('|',vt)+1, len(vt)) else '' end),
st=(case when charindex('|',vt)>0 then left(vt, charindex('|',vt)-1) else vt end),
vt1=(case when charindex('|',vt1)>0 then substring(vt1, charindex('|',vt1)+1, len(vt1)) else '' end),
st1=(case when charindex('|',vt1)>0 then left(vt1, charindex('|',vt1)-1) else vt1 end)
from tb
union all
select
t.id,
vt=(case when charindex('|',t.vt)>0 then substring(t.vt, charindex('|',t.vt)+1, len(t.vt)) else '' end),
st=(case when charindex('|',t.vt)>0 then left(t.vt, charindex('|',t.vt)-1) else t.vt end),
vt1=(case when charindex('|',t.vt1)>0 then substring(t.vt1, charindex('|',t.vt1)+1, len(t.vt1)) else '' end),
st1=(case when charindex('|',t.vt1)>0 then left(t.vt1, charindex('|',t.vt1)-1) else t.vt1 end)
from t
where len(t.vt)>0
)
select t.id, t.st, t.st1 into #tt from t
-- 结果
1 AA 888
1 BB 999
1 CC 666
2 BB 222
2 CC 333
2 AA 111
3 AA 444
3 BB 555
3 CC 666
-- 分组得出最后结果
select * from #tt
pivot (max(st1) for st in(AA,BB,CC)) b
--
1 888 999 666
2 111 222 333
3 444 555 666开着拖拉机泡妞 2012-05-19
- 打赏
- 举报
--> 测试数据：[数据库]
if object_id('[数据库]') is not null drop table [数据库]
create table [数据库](
[编号] int,
[字段1] varchar(8),
[字段2] varchar(11)
)
insert [数据库]
select 1,'AA|BB|CC','888|999|666' union all
select 2,'AA|BB|CC','111|222|333' union all
select 3,'AA|BB|CC','444|555|666'
SELECT * INTO #TEST FROM(
select [编号],
LEFT([字段1],CHARINDEX('|',[字段1])-1) AS [字段1A],
LEFT([字段2],CHARINDEX('|',[字段2])-1) AS [字段2A]
from [数据库]
UNION ALL
select [编号],
SUBSTRING([字段1],CHARINDEX('|',[字段1])+1,CHARINDEX('|',[字段1],CHARINDEX('|',[字段1]))-1) AS [字段1B],
SUBSTRING([字段1],CHARINDEX('|',[字段2])+1,CHARINDEX('|',[字段2],CHARINDEX('|',[字段2]))-1) AS [字段2B]
from [数据库]
UNION ALL
select [编号],
RIGHT([字段1],CHARINDEX('|',REVERSE([字段1]))-1) AS [字段1C],
RIGHT([字段2],CHARINDEX('|',REVERSE([字段2]))-1) AS [字段2C]
from [数据库]
)T
--如果数据格式是固定你给的那样的可以这样处理，然后再行列转换一下就好了
DECLARE @STR VARCHAR(2000)
SET @STR=''
SELECT @STR=@STR+','+[字段1A]+'=MAX(CASE WHEN [字段1A]='+QUOTENAME([字段1A],'''')+
' THEN [字段2A] END)' FROM #TEST GROUP BY [字段1A]
EXEC('SELECT [编号]'+@STR+' FROM #TEST GROUP BY [编号]')
/*
编号 AA BB CC
1 888 B|C 666
2 111 B|C 333
3 444 B|C 666
*/
