Please help me denormalize this result set.

[dojones]

THis is a once a year data extract that I need to send out. I am trying to consolidate seat information on a per customer basis if their order consists of seats in the same row. If the seat_row is different then output two records for the customer.
I am willing to use cursor if necessary. Please help!!


create table seating
(
customer_no int not null,
section varchar(30) null,
seat_row char(10) null,
seat_num char(10) null
)


insert seating values (108, 'Orchestra 2', 'N', '8')
insert seating values (108, 'Orchestra 2', 'N', '9')
insert seating values (108, 'Orchestra 2', 'N', '10')
insert seating values (111, 'Gallery Upper', 'S', '1')
insert seating values (111, 'Gallery Upper', 'S', '2')
insert seating values (111, 'Gallery Upper', 'T', '1')

result should look like this:
customer section seat_row seat_num
108 Orchestra 2 N 8,9,10
111 Gallery Upper S 1,2
111 Gallery Upper T 1

 

[donutman]

Here's the way to accomplish that:

[Blue]DECLARE[/Blue] @SN [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]2000[Gray])[/Gray]
[Blue]DECLARE[/Blue] @Loop [Blue]AS[/Blue] [Blue]int[/Blue]
[Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #Sample 
   [Gray]([/Gray]Customer  [Blue]int[/Blue][Gray],[/Gray]
    SectionNo  [Blue]varchar[/Blue][Gray]([/Gray]20[Gray])[/Gray][Gray],[/Gray]
    Seat_Row  [Blue]varchar[/Blue][Gray]([/Gray]20[Gray])[/Gray][Gray],[/Gray]
    Seat_Num  [Blue]varchar[/Blue][Gray]([/Gray]3[Gray])[/Gray][Gray])[/Gray]

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] #Sample 
   [Blue]SELECT[/Blue] 108[Gray],[/Gray] [red]'Orchestra 2'[/red][Gray],[/Gray][red]'N'[/red][Gray],[/Gray]8 [Blue]UNION[/Blue] 
   [Blue]SELECT[/Blue] 108[Gray],[/Gray] [red]'Orchestra 2'[/red][Gray],[/Gray][red]'N'[/red][Gray],[/Gray]9 [Blue]UNION[/Blue]
   [Blue]SELECT[/Blue] 108[Gray],[/Gray] [red]'Orchestra 2'[/red][Gray],[/Gray][red]'N'[/red][Gray],[/Gray]10 [Blue]UNION[/Blue]
   [Blue]SELECT[/Blue] 111[Gray],[/Gray] [red]'Gallery Upper'[/red][Gray],[/Gray][red]'S'[/red][Gray],[/Gray]1 [Blue]UNION[/Blue]
   [Blue]SELECT[/Blue] 111[Gray],[/Gray] [red]'Gallery Upper'[/red][Gray],[/Gray][red]'S'[/red][Gray],[/Gray]2 [Blue]UNION[/Blue]
   [Blue]SELECT[/Blue] 111[Gray],[/Gray] [red]'Gallery Upper'[/red][Gray],[/Gray][red]'T'[/red][Gray],[/Gray]1

[Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #Temp 
   [Gray]([/Gray]ID [Blue]int[/Blue] [Fuchsia]Identity[/Fuchsia] primary key[Gray],[/Gray]
    Customer [Blue]int[/Blue][Gray],[/Gray]
    SectionNo [Blue]varchar[/Blue][Gray]([/Gray]20[Gray])[/Gray][Gray],[/Gray]
    Seat_Row [Blue]varchar[/Blue][Gray]([/Gray]20[Gray])[/Gray][Gray],[/Gray]
    Seats [Blue]varchar[/Blue][Gray]([/Gray]2000[Gray])[/Gray][Gray])[/Gray]
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] #Temp
   [Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] Customer[Gray],[/Gray]SectionNo[Gray],[/Gray]Seat_Row[Gray],[/Gray] [Gray]NULL[/Gray] [Blue]FROM[/Blue] #Sample

[Blue]SET[/Blue] @Loop[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]ID[Gray])[/Gray] [Blue]FROM[/Blue] #Temp[Gray])[/Gray]
[Blue]WHILE[/Blue] @Loop[Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
   [Blue]SET[/Blue] @SN[Gray]=[/Gray][Gray]NULL[/Gray]
   [Blue]SELECT[/Blue] @SN[Gray]=[/Gray][Fuchsia]Coalesce[/Fuchsia][Gray]([/Gray]@SN[Gray]+[/Gray][red]', '[/red][Gray],[/Gray][red]''[/red][Gray])[/Gray][Gray]+[/Gray]Seat_Num
      [Blue]FROM[/Blue] #Sample S [Blue]INNER[/Blue] [Blue]JOIN[/Blue] #Temp T
      [Blue]ON[/Blue] S.Customer[Gray]=[/Gray]T.Customer [Gray]AND[/Gray] S.Seat_Row[Gray]=[/Gray]T.Seat_Row [Gray]AND[/Gray]
            S.SectionNo[Gray]=[/Gray]T.SectionNo [Gray]AND[/Gray] T.ID[Gray]=[/Gray]@Loop
   [Blue]UPDATE[/Blue] #Temp
      [Blue]SET[/Blue] Seats[Gray]=[/Gray]@SN  
      [Blue]WHERE[/Blue] ID[Gray]=[/Gray]@Loop
   [Blue]SET[/Blue] @Loop[Gray]=[/Gray]@Loop[Gray]-[/Gray]1
[Blue]END[/Blue]
[Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] #Temp
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #Sample
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #Temp

You will need to insert the #Temp data into your warehouse table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dojones]

Wow Karl, that was fantastic. And no cursor. THanks so much!! This works perfectly. One question, since my seat_num is actually a char datatype will the ordering of the comma separated list be determined by the physical ordering of the insert to the #sample table?

 

[donutman]

Hoping you wouldn't care about that. Ordering within a #Temp table has issues. Is it important?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dojones]

Could live without it as it is more of a display preference. Curiously it handled all of the seat concatenation in numerical order on the actual table this is being ran against. I figured this was due to the physical order of the data inserted into the #temp table.