关于sql行转列问题

Question

现有一表A
ID   StartTime       EndTime                      Type          pid
1 2016-05-29     2016-05-29                1                 1
2 2016-03-19     2016-03-19                 2                 1
3 2016-03-28     2016-03-28                 3                 1
4 2016-06-03     2016-06-03                1                 2
5 2016-05-25     2016-05-26                2                 2
6 2016-03-30     2016-03-30                3                 2想实现结果为：
ID    startType1          endType1            startType2               endType2                 startType3                 endType3      pid
1    2016-03-19  2016-03-19            2016-05-29      2016-05-29              2016-05-25                  2016-05-26         1
2    2016-03-19  2016-03-19           2016-06-03             2016-06-03              2016-03-30                   2016-03-30          2其中startType1就是原表中TYPE=1的，startType2就是TYPE=2的
请教各位。。

Answer 1

请问希望结果中, startType1,endType1 的2行值都是 2016-03-19,  2016-03-19 从何而来?

Answer 2

之前是年月日时分秒，太长，这里显示不开，所以我把时分秒删了
ID   StartTime       EndTime                      Type          pid
1        11                    111                        1                 1
2        22                    222                         2                 1
3        33                    333                         3                 1
4        41                    411                        1                 2
5        42                    422                        2                 2
6        43                    433                        3                 2想实现结果为
ID    startType1          endType1            startType2               endType2                 startType3                 endType3      pid
1          11                        111                            22                             222                            33                                 333                 1
2          41                        411                           42                             422                            43                                   433                  2

Answer 3

想实现结果为
ID   startType1 endType1  startType2       endType2       startType3 endType3  pid
1   11                 111            22                     222                   33                       333                 1
2   41                 411                    42                     422                   43                       433                 2

Answer 4

create table #t1
(ID int,StartTime int,EndTime int,Type int,pid int)insert into #t1
 select 1,11,111,1,1 union all
 select 2,22,222,2,1 union all
 select 3,33,333,3,1 union all
 select 4,41,411,1,2 union all
 select 5,42,422,2,2 union all
 select 6,43,433,3,2
declare @tsql varchar(6000)select @tsql=isnull(@tsql+',','')
            +'max(case when Type='+rtrim(Type)+' then StartTime else '''' end) ''startType'+rtrim(Type)+''', '
            +'max(case when Type='+rtrim(Type)+' then EndTime else '''' end) ''endType'+rtrim(Type)+''' '
  from (select distinct Type from #t1) t
 
select @tsql='select ID=row_number() over(order by pid),'+@tsql+',pid '
            +' from #t1 '
            +' group by pid 'exec(@tsql)/*
ID          startType1  endType1    startType2  endType2    startType3  endType3    pid
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1            11          111         22          222         33          333         1
2            41          411         42          422         43          433         2(2 row(s) affected)
*/

Answer 5

如果Type数量不固定，则将下面改为动态SQL
 ;WITH a(ID,StartTime,EndTime,[Type], pid) AS (
    select 1,11,111,1,1 UNION ALL
    select 2,22,222,2,1 UNION ALL
    select 3,33,333,3,1 UNION ALL
    select 4,41,411,1,2 UNION ALL
    select 5,42,422,2,2 UNION ALL
    select 6,43,433,3,2 
 )
 SELECT pid,StartType1,EndType1,StartType2,EndType2,StartType3,EndType3  FROM (
     SELECT pid,col,datavalue FROM (
         SELECT *,b.title+'Type'+CONVERT(VARCHAR,a.[Type]) AS col FROM a
         CROSS APPLY(VALUES('Start',a.StartTime),('End',a.EndTime)) b(title,datavalue)
     ) c 
 ) d
 PIVOT(MAX(datavalue) FOR col IN (StartType1,EndType1,StartType2,EndType2,StartType3,EndType3)) e

Answer 6

动态SQL无法转视图,建议用存储过程.

Answer 7

create table #t1(ID int,StartTime int,EndTime int,Type int,pid int)
 
insert into #t1
 select 1,11,111,1,1 union all
 select 2,22,222,2,1 union all
 select 3,33,333,3,1 union all
 select 4,41,411,1,2 union all
 select 5,42,422,2,2 union all
 select 6,43,433,3,2 union all
 select 6,44,443,4,2 
DECLARE @cols NVARCHAR(500),@sql NVARCHAR(max)
SELECT @cols=isnull(@cols+',','')+col FROM (
    SELECT DISTINCT b.title+'Type'+CONVERT(VARCHAR,a.[Type]) AS col FROM #t1 AS a
    CROSS APPLY(VALUES('Start',a.StartTime),('End',a.EndTime)) b(title,datavalue)
) c 
SELECT @cols
 
 SET @sql=N'SELECT pid,'+@cols+ ' FROM (
     SELECT pid,col,datavalue FROM (
         SELECT *,b.title+''Type''+CONVERT(VARCHAR,a.[Type]) AS col FROM #t1 AS a
         CROSS APPLY(VALUES(''Start'',a.StartTime),(''End'',a.EndTime)) b(title,datavalue)
     ) c 
 ) d
 PIVOT(MAX(datavalue) FOR col IN ('+@cols+ ')) e'
 
EXEC(@sql)/*
pid EndType1 EndType2 EndType3 EndType4 StartType1 StartType2 StartType3 StartType4
1 111 222 333 NULL 11 22 33 NULL
2 411 422 433 443 41 42 43 44
*/