PIVOT / UNPIVOT in SQL Server 2008

Question

Hello

I got child / parent tables as below.

MasterTable:

MasterID, Description

ChildTable

ChildID, MasterID, Description.

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

if (MasterID : 1 got x child records)

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

Thanks

Answer 1

Hi, here is a T_SQL, assuming this:

• You do not know how many columns may appear in the results.
• The Pivot elements can vary (thats why the first assumption).
• You need the specific order 'ChildId1, ChilDesc1, ChildId2, ChildDesc2... asd so ever'

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

EDIT: The result is this:

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

Answer 2

It greatly depends on whether the number of crosstabbed columns is fixed. If they are, then you can simply do something like:

Select ParentDesc
    , [1] As ChildId1
    , [Description1] As ChildDescription1
    , [2] As ChildId2
    , [Description2] As ChildDescription2
    , [3] As ChildId3
    , [Description3] As ChildDescription3
From    (
        Select C.Id As ChildId, C.Description As ChildDesc, P.Description As ParentDesc
        From ChildItems As C
            Join ParentItems As P
                On P.Id = C.ParentId
        ) As C
Pivot   (
        Count(ChildId)
        For ChildId In([1],[2],[3])
        ) As PVT0
Pivot   (
        Count(ChildDesc)
        For ChildDesc In([Descripion1],[Descripion2],[Descripion3])
        ) As PVT1

There is also a way of achieving similar results use CASE functions.

However, if what you want is to have the number of crosstab columns determined at runtime, then the only means to do that inside of SQL Server is to use some fugly dynamic SQL. This is outside the realm of SQL Server's primary purpose which is to serve up data (as opposed to information). If you want a dynamic crosstab, I would recommend not doing it in SQL Server but instead use a reporting tool or build your result set in a middle-tier component.