if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
go
create table #tb1([ID1] varchar(2))
insert #tb1
select '03'union all
select '04'union all
select '08'union all
select '21'union all
select '22'union all
select '24'union all
select '27'
select * from #tb1
转换成下面 多字段的一行,精华帖上是列转行，但不是一列转换成多字段的一行，请问用语句如何完成，谢谢。
XM N1 N2 N3 N4 N5 N6 N7
ID1 3 4 8 21 22 24 27
------解决思路----------------------
select 'ID1' AS XM,
SUM(case when id1='03' then CONVERT(int,ID1)end) as [N1],
SUM(case when id1='04' then CONVERT(int,ID1)end) as [N2],
SUM(case when id1='08' then CONVERT(int,ID1)end) as [N3],
SUM(case when id1='21' then CONVERT(int,ID1)end) as [N4],
SUM(case when id1='22' then CONVERT(int,ID1)end) as [N5],
SUM(case when id1='24' then CONVERT(int,ID1)end )as [N6],
SUM(case when id1='27' then CONVERT(int,ID1)end )as [N7]
from #tb1
------解决思路----------------------
select 'ID1' AS XM,
SUM(case when rId=1 then cast(ID1 as int) end) as [N1],
SUM(case when rId=2 then cast(ID1 as int) end) as [N2],
SUM(case when rId=3 then cast(ID1 as int) end) as [N3],
SUM(case when rId=4 then cast(ID1 as int) end) as [N4],
SUM(case when rId=5 then cast(ID1 as int) end) as [N5],
SUM(case when rId=6 then cast(ID1 as int) end )as [N6],
SUM(case when rId=7 then cast(ID1 as int) end )as [N7]
from(
select *,row_number() over(order by getdate() asc) as rId from #tb1
) as a
------解决思路----------------------
if object_id('tempdb.dbo.#tb2') is not null drop table #tb2
GO
SELECT Convert(int,ID1) ID1,
'N' + Convert(varchar(11),
ROW_NUMBER() OVER(ORDER BY ID1)
) N
INTO #tb2
FROM #tb1
GO
DECLARE @sql varchar(max),
@columns varchar(max)
SET @columns = ''
SELECT @columns = @columns + ', [' + Convert(varchar(11),n) + ']'
FROM #tb2
SET @sql = '
SELECT ''ID1'' AS XM, *
FROM #tb2
PIVOT (
Max (ID1)
FOR N IN ( ' + SubString(@columns, 3, Len(@columns)-2) + ')
) AS p'
PRINT @sql
EXEC (@sql)SELECT 'ID1' AS XM, *
FROM #tb2
PIVOT (
Max (ID1)
FOR N IN ( [N1], [N2], [N3], [N4], [N5], [N6], [N7])
) AS p
XM N1 N2 N3 N4 N5 N6 N7
---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
ID1 3 4 8 21 22 24 27