34,590
社区成员
发帖
与我相关
我的任务
分享请教一个行转列的SQL
CREATE TABLE #TA(cid VARCHAR(20),xh INT,name VARCHAR(20), val INT)
INSERT INTO #TA
SELECT 'A01', 1, 'NCPB', 12 UNION ALL
SELECT 'A01', 2, 'GTRF', 23 UNION ALL
SELECT 'A01', 3, 'HYRT', 9 UNION ALL
SELECT 'A01', 4, 'KOUU', 4 UNION ALL
SELECT 'B01', 1, 'JUGH', 3 UNION ALL
SELECT 'B01', 2, 'GTRF', 1 UNION ALL
SELECT 'C01', 1, 'HYRT', 10 UNION ALL
SELECT 'D01', 1, 'NCPB', 9 UNION ALL
SELECT 'D01', 2, 'HYRT', 4
name列不固定
转换结果:
...全文
请发表友善的回复…
发表回复
Hello World, 2020-12-07
- 打赏
- 举报
SELECT cid, MAX(N1) N1, MAX(V1) v1, MAX(N2) n2, MAX(V2) v2, MAX(N3) n3, MAX(V3) v3, MAX(N4) n4, MAX(V4) v4
FROM ( SELECT cid, p.[1] N1, NULL AS V1, p.[1] AS N2, NULL AS V2, p.[3] AS N3, NULL AS V3, p.[4] AS N4, NULL AS V4
FROM (SELECT cid, [name], xh FROM #ta) a
PIVOT ( MAX(name)
FOR xh IN ([1], [2], [3], [4])) p
UNION ALL
SELECT cid, NULL AS N1, p.[1] AS V1, NULL AS N2, p.[2] AS V2, NULL AS N3, p.[3] AS V3, NULL AS N4, p.[4] AS V4
FROM (SELECT cid, [val], xh FROM #ta) a
PIVOT ( MAX(val)
FOR xh IN ([1], [2], [3], [4])) p) t
二月十六 版主 2020-12-07
- 打赏
- 举报
name是固定这几个吗？
北冥小渔夫 2020-12-07
- 打赏
- 举报
非常感谢各位大佬的回复，看了大佬们的代码思维又有新的拓展，
RINK_1 2020-12-07
- 打赏
- 举报
DECLARE @SQL VARCHAR(MAX)
WITH CTE
AS
(SELECT *
FROM
(SELECT CID,XH,NAME,'NAME'+CAST(XH AS VARCHAR) AS TYPE FROM #TA
UNION ALL
SELECT CID,XH,CAST(VAL AS VARCHAR),'VAL'++CAST(XH AS VARCHAR) FROM #TA) AS A)
SELECT @SQL=ISNULL(@SQL+',','')+QUOTENAME(TYPE)
FROM CTE
GROUP BY TYPE,XH
ORDER BY XH,TYPE
SET @SQL='WITH CTE
AS
(SELECT *
FROM
(SELECT CID,XH,NAME,''NAME''+CAST(XH AS VARCHAR) AS TYPE FROM #TA
UNION ALL
SELECT CID,XH,CAST(VAL AS VARCHAR),''VAL''++CAST(XH AS VARCHAR) FROM #TA) AS A)
SELECT *
FROM (SELECT CID,NAME,TYPE FROM CTE) AS A
PIVOT (MAX(NAME) FOR TYPE IN('+@SQL+')) AS B'
EXEC(@SQL)
雨夹雪 2020-12-07
- 打赏
- 举报
互相学习
锟斤拷锟斤拷 2020-12-07
- 打赏
- 举报
早点看到这个好答案我就不用浪费一天时间实现那个弱智思路了雨夹雪 2020-12-07
- 打赏
- 举报
CREATE TABLE #TA(cid VARCHAR(20),xh INT,name VARCHAR(20), val INT)
INSERT INTO #TA
SELECT 'A01', 1, 'NCPB', 12 UNION ALL
SELECT 'A01', 2, 'GTRF', 23 UNION ALL
SELECT 'A01', 3, 'HYRT', 9 UNION ALL
SELECT 'A01', 4, 'KOUU', 4 UNION ALL
SELECT 'B01', 1, 'JUGH', 3 UNION ALL
SELECT 'B01', 2, 'GTRF', 1 UNION ALL
SELECT 'C01', 1, 'HYRT', 10 UNION ALL
SELECT 'D01', 1, 'NCPB', 9 UNION ALL
SELECT 'D01', 2, 'HYRT', 4
DECLARE @sql VARCHAR(max)=''
SELECT
@sql=@sql
+',max(case when xh='+CONVERT(VARCHAR(10),xh)+' then name else null end) as name'+CONVERT(VARCHAR(10),xh)
+',max(case when xh='+CONVERT(VARCHAR(10),xh)+' then val else null end) as val'+CONVERT(VARCHAR(10),xh) +CHAR(13)
FROM
(
SELECT xh FROM #TA GROUP BY xh
) a
SET @sql='select cid'+@sql+' from #TA group by cid'
EXEC(@sql)
DROP TABLE #TA
锟斤拷锟斤拷 2020-12-07
- 打赏
- 举报
无论有多少个不同CID，无论有多少组NAME和VAL，用这个应该都行了
declare @sql varchar(max)='',@sql1 varchar(1000),@cid varchar(50),@max int,@i int = 1,
@cols int,@maxcol int
---查一共有多少不同的CID
select @maxcol = MAX(a1.cols) from (select a.cid,COUNT(*) cols from #TA a group by a.cid) a1
select @max=MAX(a.rownum) from (select ROW_NUMBER() over(order by cid) rownum from #TA group by cid) a
while(@i<=@max)----第i个不同的CID
begin
select top 1 @cid=cid from #TA where cid not in (select top (@i-1) cid from #TA group by cid) group by cid
select @cols = a1.cols from(select COUNT(*) cols from #TA a where a.cid = @cid) a1
set @sql1 = 'union all select * from (select cid from #TA where cid='''+ @cid +''' group by cid) a'
select @sql1 =
@sql1 +
' left join (select '''+ name +''' name,'+cast(val as varchar(20))+' val)' +
QUOTENAME(name)+'on 1=1' from #TA where cid=@cid
while(@cols<@maxcol)
begin
set @sql1 = @sql1 + ' left join (select NULL name,Null val) addnullcolumn'+ cast(@cols as varchar(5)) +' on 1=1'
set @cols = @cols +1
end
set @sql = @sql + @sql1
set @i = @i + 1
end
set @sql = SUBSTRING(@sql,11,8000)
print(@sql)
exec(@sql)
北冥小渔夫 2020-12-07
- 打赏
- 举报
name列不固定
RINK_1 2020-12-07
- 打赏
- 举报
CREATE TABLE #TA(cid VARCHAR(20),xh INT,name VARCHAR(20), val INT)
INSERT INTO #TA
SELECT 'A01', 1, 'NCPB', 12 UNION ALL
SELECT 'A01', 2, 'GTRF', 23 UNION ALL
SELECT 'A01', 3, 'HYRT', 9 UNION ALL
SELECT 'A01', 4, 'KOUU', 4 UNION ALL
SELECT 'B01', 1, 'JUGH', 3 UNION ALL
SELECT 'B01', 2, 'GTRF', 1 UNION ALL
SELECT 'C01', 1, 'HYRT', 10 UNION ALL
SELECT 'D01', 1, 'NCPB', 9 UNION ALL
SELECT 'D01', 2, 'HYRT', 4
SELECT *
FROM
(SELECT CID,NAME,'NAME'+CAST(XH AS VARCHAR) AS TYPE FROM #TA
UNION ALL
SELECT CID,CAST(VAL AS VARCHAR),'VAL'++CAST(XH AS VARCHAR) FROM #TA) AS A
PIVOT
(MAX(NAME) FOR TYPE IN([NAME1],[VAL1],[NAME2],[VAL2],[NAME3],[VAL3],[NAME4],[VAL4])) AS B
