List large list fields in a tabular format

[jtarry]

I need some advice.

I require to present table information
Table contains

BOM parent BOM Child Qty
1234 AAA 1
1234 BBB 3
1234 CCC 6
1235 AAA 2
1235 CCC 2
etc etc etc

I need to display this info in the following format in Excel

Bom Child BOM parent BOM parent BOM parent BOM parent
1234 1235 XXX XXW
AAA 1 2 1 3
BBB 3 0 1 2
CCC 6 2 1 0

The problem is this could be upto 300 Bom parents, hence need 301 columns!

Formating is no issue so a csv file would be fine.

 

[dhulbert]

You could look at pivoting the data in SQL but its probably easier to just import the data into Excel then use a pivot table, if you are in Excel 2007 it will handle the 300+ Columns.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[markros]

You need to use dynamic PIVOT in SQL Server 2005+.

declare @Cols nvarchar(max), @SQL nvarchar(max)

;with cte as (select distinct [BOM Parent] from Table)

set @Cols = stuff((select ', ' + quotename([BOM Parent]) 
from cte ORDER BY [BOM Parent] FOR XML PATH('')),1,2,'')

set @SQL = 'select *
from BOMInfo 
PIVOT (sum(Qty) FOR [BOM Parent] IN (' + @Cols +')) pvt'

execute (@SQL)[/code

From the top of my head.

[url=http://www.pluralsight.com/main/olt/?ac=1905603345]PluralSight Learning Library[/url]