sql server - SQL Pivot and combine uneven number of records from row to column based on value -

sql server - SQL Pivot and combine uneven number of records from row to column based on value -

alrighty, have vicious stored proc combines info 10 or more tables. have simplified procedure quite bit example, need @ point pivot info row column based on value in ls#. tricky part need group/combine info 1 row based on values in other 5 columns. here's info stored proc returns:

calid id gbid crs# sec# crsdesc ls# 12 2 1449 239002 000001 reading 02.re.com 12 2 1449 239002 000001 reading 02.re.dwr 12 2 1449 239002 000001 reading be.k5.a.01 12 2 1449 239002 000001 reading be.k5.b.01 12 2 1449 239002 000001 reading be.k5.c.01

what want is:

calid id gbid crs# sec# crsdesc ls# lsbe# 12 2 1449 239002 000001 reading 02.re.com be.k5.a.01 12 2 1449 239002 000001 reading 02.re.dwr be.k5.b.01 12 2 1449 239002 000001 reading null be.k5.c.01

at times, there more non records non records, , vice versa, need available rows show up, blank or null in appropriate field. i'm sure easy sql genius out there, totally eluding me.

ok, kinda cumbersome, works:

;with cte ( select *, case when [ls#] 'be%' 1 else 0 end isbe, rn = row_number() over(partition calid, id, [gbid], [crs#], [sec#], [crsdesc], case when [ls#] 'be%' 1 else 0 end order [ls#]) yourtable ) select isnull(a.[calid],b.[calid]) [calid], isnull(a.[id],b.[id]) [id], isnull(a.[gbid],b.[gbid]) [gbid], isnull(a.[crs#],b.[crs#]) [crs#], isnull(a.[sec#],b.[sec#]) [sec#], isnull(a.[crsdesc],b.[crsdesc]) [crsdesc], a.[ls#] [ls#], b.[ls#] [lsbe#] ( select * cte isbe = 0) total bring together ( select * cte isbe = 1) b on a.calid = b.calid , a.id = b.id , a.gbid = b.gbid , a.[sec#] = b.[sec#] , a.crsdesc = b.crsdesc , a.rn = b.rn;

here sqlfiddle demo of it. , results are:

╔═══════╦════╦══════╦════════╦══════╦═════════╦═══════════╦════════════╗ ║ calid ║ id ║ gbid ║ crs# ║ sec# ║ crsdesc ║ ls# ║ lsbe# ║ ╠═══════╬════╬══════╬════════╬══════╬═════════╬═══════════╬════════════╣ ║ 12 ║ 2 ║ 1449 ║ 239002 ║ 1 ║ reading ║ 02.re.com ║ be.k5.a.01 ║ ║ 12 ║ 2 ║ 1449 ║ 239002 ║ 1 ║ reading ║ 02.re.dwr ║ be.k5.b.01 ║ ║ 12 ║ 2 ║ 1449 ║ 239002 ║ 1 ║ reading ║ null ║ be.k5.c.01 ║ ╚═══════╩════╩══════╩════════╩══════╩═════════╩═══════════╩════════════╝

sql-server pivot