SQL Server 2008中的PIVOT/UNPIVOT

问题描述

我得到了如下的子表/父表.

I got child / parent tables as below.

MasterTable:

MasterTable:

MasterID, Description

ChildTable

ChildTable

ChildID, MasterID, Description.

使用PIVOT/UNPIVOT如何在单行中获取如下结果.

Using PIVOT / UNPIVOT how can i get result as below in single row.

如果(MasterID:1个有x个子记录)

if (MasterID : 1 got x child records)

MasterID, ChildID1, Description1, ChildID2, Description2....... ChildIDx, Descriptionx

谢谢

推荐答案

这是一个T_SQL，假设这样做:

here is a T_SQL, assuming this:

• 您不知道结果中可能出现多少列.
• Pivot元素可以变化(这就是第一个假设的原因).
• 您需要特定的顺序'ChildId1，ChilDesc1，ChildId2，ChildDesc2 ... asd永远如此'

声明@MaxCountOfChild int

DECLARE@MaxCountOfChild int

-- Obtaining Maximum times a Master is used by its children
SELECT TOP 1 @MaxCountOfChild= count(*)
FROM ChildTable
GROUP BY MasterID
order by count(*) DESC


--With that number, create a string for the Pivot elements
--if you want them in the order Id1-Desc1-Id2-Desc2
DECLARE
    @AuxforReplacing nvarchar(MAX),
    @ChildIdsandDescs nvarchar(MAX),
    @PivotElements nvarchar(MAX),
    @Counter int,
    @sql nvarchar(MAX)

SET @Counter=0
SET @AuxforReplacing=''
SET @ChildIdsandDescs=''
SET @PivotElements=''

WHILE (@Counter < @MaxCountOfChild)
begin
    SET @Counter=@Counter +1
    SET @PivotElements=@PivotElements + '[' +convert(varchar, @Counter)+ '],'
    SET @AuxforReplacing=@AuxforReplacing +  '[' +convert(varchar, @Counter)+ '] as ' + convert(varchar, @Counter) + ','
    SET @ChildIdsandDescs=@ChildIdsandDescs + '[ChildID' + convert(varchar, @Counter)+ '],[ChildDesc' + convert(varchar, @Counter) +'],'

end
SET @PivotElements=LEFT(@PivotElements, len(@PivotElements)-1)
SET @ChildIdsandDescs=LEFT(@ChildIdsandDescs, len(@ChildIdsandDescs)-1)
SET @AuxforReplacing=LEFT(@AuxforReplacing, len(@AuxforReplacing)-1)


--print REPLACE(@AuxforReplacing, 'as ', 'as ChildId')

--print @ChildIds
--print @PivotElements


SET @sql = N'
WITH AuxTable (Masterdesc,ChildId, MasterId,ChildDesc,  NumeroenMaster)
AS
(
SELECT M.Description as MasterDesc, C.*, RANK() OVER (PARTITION BY M.MasterId ORDER BY M.MasterId, ChildId)
FROM  MasterTable M
    INNER JOIN ChildTable C
        ON M.MasterId=C.MasterId
)

SELECT TablaMaster.MasterId,' + @ChildIdsandDescs + '
FROM
(
    SELECT MasterId, ' + REPLACE(@AuxforReplacing, 'as ', 'as ChildId') + '
    FROM (
    SELECT MasterId, NumeroenMaster, ChildId
    FROM AuxTable) P
    PIVOT
    (
    MAX (ChildId)
    FOR NumeroenMaster IN (' + @PivotElements +')
    ) AS pvt) As TablaMaster
INNER JOIN
(
    SELECT MasterId, ' + REPLACE(@AuxforReplacing, 'as ', 'as ChildDesc') + '
    FROM (
    SELECT MasterId, NumeroenMaster, ChildDesc
    FROM AuxTable) P
    PIVOT
    (
    MAX (ChildDesc)
    FOR NumeroenMaster IN (' + @PivotElements +')
    ) AS pvt) As TablaChild
ON TablaMaster.MasterId= TablaChild.MasterId'

EXEC sp_executesql @sql

结果是这样:

MasterId ChildID1 ChildDesc1 ChildID2 ChildDesc2  ChildID3 ChildDesc3 ChildID4 ChildDesc4
-------- -------- ---------- -------- ----------- -------- ---------- -------- ---------
1           1      Child1       2      Child2     NULL        NULL       NULL      NULL
2           3      Child3       4      Child4      7          Child7      8      Child8
3           5      Child5       6      Child5     NULL        NULL       NULL      NULL

Asumming this in the table ChildTable:
ChildId  MasterId  ChildDesc
-------  --------  ---------
1      1       Child1
2      1       Child2
3      2       Child3
4      2       Child4
5      3       Child5
6      3       Child5
7      2       Child7
8      2       Child8

这篇关于SQL Server 2008中的PIVOT/UNPIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！