runninggeek runninggeek - 1 month ago 11
SQL Question

How to flatten rows into columns and specify column names

I have seen a lot of the answers about pivoting and other solutions for transforming a 1:M relationship from many rows to many columns, but I have not seen one that addresses my specific scenario. I'm using SQL Server 2014.

I have two tables: Attendee and AttendeeChild. Here are the relevant columns in each:

Attendee



Id

AttendeeChild



Id

AttendeeId

Age

Name

Gender

An Attendee can have any number of "child" rows. For each attendee, I want to flatten the AttendeeChild rows into columns so the the output looks like this for AttendeeId = 1;

AttendeeId Child1Age Child1Name Child1Gender Child2Age Child2Name Child2Gender
---------- --------- ---------- ------------ --------- ---------- ------------
1 10 Sam Boy 9 Sally Girl


The set of child related attributes should continue to expand the number of columns for each AttendeeChild row and the column header should keep the pattern "Child{#}{Attribute}" where {#} is an incrementing counter of the next child row from AttendeeChild and {Attribute} is either "Name", "Age" or "Gender".

I hope this is enough information to convey the issue.

Answer

Another Option is to use some XML to convert the data to a EAV Structure (Entity Attribute Value)

This approach is very dynamic, and will generate the appropriate number of "groups" in the proper order.

Declare @AttendeeChild table (Id int,AttendeeId int,Age Int,Name varchar(50),Gender varchar(25))
Insert Into @AttendeeChild values
(1,1,10,'Sam','Boy'),
(2,1,9,'Sally','Girl'),
(2,2,9,'Sue','Boy')

-- Convert Data to EAV Structure'ish
Declare @XML xml = (Select *,GrpSeq=Row_Number() over (Partition By AttendeeId Order By Age Desc) from @AttendeeChild for XML RAW)
Select AttendeeId = r.value('@AttendeeId','int')
      ,GrpSeq     = r.value('@GrpSeq','int')
      ,ColSeq     = Row_Number() over (Partition By r.value('@AttendeeId','int') Order By (Select NULL))
      --,Item     = attr.value('local-name(.)','varchar(100)')
      ,Element    = 'Child'+r.value('@GrpSeq','varchar(10)')+attr.value('local-name(.)','varchar(100)')
      ,Value      = attr.value('.','varchar(max)') 
 Into  #Temp
 From  @XML.nodes('/row') as A(r)
 Cross Apply A.r.nodes('./@*') AS B(attr)
 Where attr.value('local-name(.)','varchar(100)') not in ('ID','AttendeeId','GrpSeq')

-- Get Cols in correct Order
Declare @Cols varchar(max) = Stuff((Select ',' + QuoteName(Element) 
                                     From  (Select Distinct Top 100 Percent  ColSeq,Element From #Temp Order By ColSeq ) A
                                     For XML Path(''), Type
                                    ).value('.', 'varchar(max)'),1,1,'')

-- Execute Dynamic Pivot
Declare @SQL varchar(max) = '
Select *
 From (Select AttendeeId,Element,Value From #Temp) T
 Pivot (
        max(Value)
        For [Element] in (' + @Cols + ')
       ) P '

Exec(@SQL)

Returns

enter image description here