PIVOT unknown number of rows

[JD1866DSC]

Hi,

I'm setting up a new data extraction process which will allow the user to pick certain columns, tables and databases to extract data.

I have a stored procedure that brings me back a format like this

ProjectName AccountSpecific SchemaName TableName ColumnName
Test Project N Test Schema Table1 Col1
Test Project N Test Schema Table1 Col2
Test Project N Test Schema Table1 Col3
Test Project N Test Schema Table1 Col4
Test Project N Test Schema Table2 Col1
Test Project N Test Schema Table2 Col14

SELECT
P.ProjectName
,P.AccountSpecific
,S.SchemaName
,T.TableName
,C.ColumnName
FROM
dbo.Projects P
JOIN
dbo.Schemas S ON P.schemaID = S.schemaID
JOIN
dbo.Tables T ON S.schemaID = T.schemaID
JOIN
dbo.Columns C ON T.TableID = C.TableID

How can I pivot this data so I can build a query like this

SELECT
col1
,col2
,col3
,col4
FROM
dbo.Table1

Any ideas on this would be most appreciated, am I going about the design the best way?

 

[mrdenny]

What version of SQL Server are you using?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JD1866DSC]

SQL Server 2005 enterprise

 

[mrdenny]

You'll want to take a look at the PIVOT command. You don't appear to have a value assigned to each row in the source table. What value will you be displaying within the recordset?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog