请教一个行转列的SQL

2020-12-06 10:43:10 399 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Hive sql 行转列 Hive 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列不固定

转换结果:

...全文

从高德、百度地图下载POI数据？用这个工具5分钟搞定GCJ02/BD09到WGS84的坐标纠偏本文详细解析了高德、百度地图POI数据坐标偏移问题，并提供了从GCJ02/BD09到WGS84的高效转换方案。通过开源工具如coordtransform和ArcGIS等专业软件，实现毫米级精度的坐标纠偏，适用于商业选址、GIS分析等场景，显著提升数据准确性和工作效率。阅读详情

北冥小渔

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

引用 6 楼雨夹雪的回复:



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

打赏
举报



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

打赏
举报

引用 1 楼二月十六的回复:

name是固定这几个吗？

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

请教一个行转列的SQL

相关推荐