问个sql语句，有点像行转列

[kevn]

如下，p_id 是人员ID，a，b，c是货品名称，jidu是季度，分为1,2,3,4。

create table #t(p_id varchar(10),a int,b int,c int,jidu int)

insert into #t
select '1',20,30,49,1 union all
select '1',21,35,49,2 union all
select '1',20,33,234,3 union all
select '1',40,220,22,4 union all

select '2',240,34,495,1 union all
select '2',220,356,494,2 union all
select '2',120,50,2,3 union all
select '2',202,30,23,4  


--想要的结果
--p_id    a-1 a-2 a-3 a-4 b-1 b-2 b-3 b-4 c-1 c-2 c-3 c-4
--1        20  21  20  40  30  35  33  220 49  49  234 22
--2       240 220 120 202 34  356 50  30  495 494 2   23

就是要一个人对应一条数据，这条数据分别是a货的1,2,3,4季度的，依次是b,c的1,2,3,4的

[kevn]

sql2005

[yoan2014]

SELECT p_id,
		(SELECT b.a FROM #t AS b WHERE b.p_id = a.p_id AND b.jidu = 1) AS [a-1],
		(SELECT b.a FROM #t AS b WHERE b.p_id = a.p_id AND b.jidu = 2) AS [a-2],
		(SELECT b.a FROM #t AS b WHERE b.p_id = a.p_id AND b.jidu = 3) AS [a-3],
		(SELECT b.a FROM #t AS b WHERE b.p_id = a.p_id AND b.jidu = 4) AS [a-4]
 FROM #t AS a GROUP BY a.p_id
 
 --結果
 p_id       a-1         a-2         a-3         a-4
---------- ----------- ----------- ----------- -----------
1          20          21          20          40
2          240         220         120         202

(2 row(s) affected)

剩下的自己補一下

[xxfvba]

declare @s varchar(max) select @s=ISNULL(@s+',','')+QUOTENAME('a'+convert(varchar,jidu))+'=max(case when jidu='+QUOTENAME(jidu,'''')+ 'then a end),' +QUOTENAME('b'+convert(varchar,jidu))+'=max(case when jidu='+QUOTENAME(jidu,'''')+ 'then b end),' +QUOTENAME('c'+convert(varchar,jidu))+'=max(case when jidu='+QUOTENAME(jidu,'''')+ 'then c end)' from #t group by jidu set @s='select p_id,'+@s+'from #t group by p_id' exec(@s)

[kevn]

引用 2 楼 yoan2014 的回复:

SELECT p_id,
		(SELECT b.a FROM #t AS b WHERE b.p_id = a.p_id AND b.jidu = 1) AS [a-1],
		(SELECT b.a FROM #t AS b WHERE b.p_id = a.p_id AND b.jidu = 2) AS [a-2],
		(SELECT b.a FROM #t AS b WHERE b.p_id = a.p_id AND b.jidu = 3) AS [a-3],
		(SELECT b.a FROM #t AS b WHERE b.p_id = a.p_id AND b.jidu = 4) AS [a-4]
 FROM #t AS a GROUP BY a.p_id
 
 --結果
 p_id       a-1         a-2         a-3         a-4
---------- ----------- ----------- ----------- -----------
1          20          21          20          40
2          240         220         120         202

(2 row(s) affected)

剩下的自己補一下

结果是正确的，还有没有更好的方法呢，我的a,b,c货物有20多个，每个都会分成4列

[jiajiaren]

IF OBJECT_ID('tempdb..#t') IS NOT  NULL
DROP TABLE #t
Go 

create table #t(p_id varchar(10),a int,b int,c int,jidu int)

insert into #t
select '1',20,30,49,1 union all
select '1',21,35,49,2 union all
select '1',20,33,234,3 union all
select '1',40,220,22,4 union all

select '2',240,34,495,1 union all
select '2',220,356,494,2 union all
select '2',120,50,2,3 union all
select '2',202,30,23,4  
 
 
 ----------------------------------------------查询---------------------------------------------------------------
SELECT p_id, MAX(ISNULL(CASE  jidu WHEN 1 THEN  a  END,0)) 'a-1', MAX(ISNULL(CASE jidu WHEN 2 THEN a END,0)) 'a-2' , MAX(ISNULL(CASE jidu WHEN 3 THEN a END,0)) 'a-3'  
  , MAX(ISNULL(CASE jidu WHEN 4 THEN a END,0)) 'a-4' , MAX(ISNULL(CASE jidu WHEN 1 THEN b END,0)) 'b-1', MAX(ISNULL(CASE jidu WHEN 2 THEN b END,0)) 'b-2' , MAX(ISNULL(CASE jidu WHEN 3 THEN b END,0)) 'b-3'  
  , MAX(ISNULL(CASE jidu WHEN 4 THEN b END,0)) 'b-4' , MAX(ISNULL(CASE jidu WHEN 1 THEN c END,0)) 'c-1', MAX(ISNULL(CASE jidu WHEN 2 THEN c END,0)) 'c-2' , MAX(ISNULL(CASE jidu WHEN 3 THEN c END,0)) 'c-3'  
  , MAX(ISNULL(CASE jidu WHEN 4 THEN c END,0)) 'c-4'   FROM #t GROUP BY  p_id

 /*
 p_id       a-1         a-2         a-3         a-4         b-1         b-2         b-3         b-4         c-1         c-2         c-3         c-4
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1          20          21          20          40          30          35          33          220         49          49          234         22
2          240         220         120         202         34          356         50          30          495         494         2           23

(2 行受影响)

 */

[yoan2014]

 SELECT p_id,
        jidu,
        bb = attribute,
        Num = VALUE
        INTO #d
 FROM   #t
        UNPIVOT(VALUE FOR attribute IN ([a], [b], [c])) AS UPV --在這裏寫一遍吧，沒辦法了
 
 
 SELECT *
 FROM   #d
 
 
 DECLARE @s VARCHAR(MAX)	
 SELECT @s = ISNULL(@s + ',', '') + QUOTENAME(bb + '-' + CONVERT(VARCHAR, jidu))
        + '=max(case when jidu=' + QUOTENAME(jidu, '''') + 'then num end)'
 FROM   #d
 GROUP BY
        jidu,
        bb
 
 PRINT @s        
 SET @s = 'select p_id,' + @s + 'from #d group by p_id'
 
 EXEC (@s) 

[yoan2014]

有點問題

[yoan2014]

SELECT p_id,
        jidu,
        bb = attribute,
        Num = VALUE
        INTO #d
 FROM   #t
        UNPIVOT(VALUE FOR attribute IN ([a], [b], [c])) AS UPV --在這裏寫一遍吧，沒辦法了
  
  SELECT * FROM #d
 SELECT p_id,jidu ,bb,sum(num)
 FROM   #d GROUP BY  p_id,jidu , bb
  
  
 DECLARE @s VARCHAR(MAX) 
 
 
 SELECT @s = ISNULL(@s + ',', '') + QUOTENAME(bb + '-' + CONVERT(VARCHAR, jidu))
        + '=sum(case when jidu=' + QUOTENAME(a.jidu, '''') + 'and bb ='+ QUOTENAME(a.bb, '''') + 'then num else 0 end)'
 FROM   (SELECT DISTINCT jidu , bb FROM #d )AS a ORDER BY bb,jidu

  
 PRINT @s        
 SET @s = 'select p_id,' + @s + 'from #d group by p_id '
  
 EXEC (@s) 
 -----结果---------
 p_id       a-1         a-2         a-3         a-4         b-1         b-2         b-3         b-4         c-1         c-2         c-3         c-4
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1          20          21          20          40          30          35          33          220         49          49          234         22
2          240         220         120         202         34          356         50          30          495         494         2           23

(2 行受影响)