Dynamic Column Names

Hi guys 'n gals,

I am trying to achieve something a bit complex (or at least appears to be for me).

I have the following table structure:

UID, GroupID, ColumnName, ColumnValue

here is some example data:

UID, GroupId, ColumnName, ColumnValue
1, 1, MAC Address, 8a7sd87sad
2, 1, IP Address, 192.168.1.100
3, 1, Name, John
4, 2, MAC Address, 09a8sd098as
5, 2, Name, Steven

and here is what I would like the example to come out like:

GroupID, MAC Address, IP Address, Name
1, 8a7sd87sad, 192.168.1.100, John
2, 09a8sd098as, NULL, Steven

It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...

I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:

Code Snippet

DECLARE @.ColumnName varchar(128)
DECLARE @.Sql varchar(255)
SELECT @.ColumnName = [ColumnName] FROM tblSourceData
SELECT @.Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@.ColumnName+'] FROM tblSourceData'
EXEC (@.Sql)

Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?

Regards,
Justin

You need not to have dynamic SQL, you are trying to achieve the table pivoting..

Code Snippet

Create Table #data (

[UID] Varchar(100) ,

[GroupId] Varchar(100) ,

[ColumnName] Varchar(100) ,

[ColumnValue] Varchar(100)

);

Insert Into #data Values('1','1','MAC Address','8a7sd87sad');

Insert Into #data Values('2','1','IP Address','192.168.1.100');

Insert Into #data Values('3','1','Name','John');

Insert Into #data Values('4','2','MAC Address','09a8sd098as');

Insert Into #data Values('5','2','Name','Steven');

On SQL server 2000/2005,

Code Snippet

Select

GroupId,

Max(Case When[ColumnName] = 'MAC Address' Then ColumnValue End) as [MAC Address],

Max(Case When[ColumnName] = 'IP Address' Then ColumnValue End) as [IP Address],

Max(Case When[ColumnName] = 'Name' Then ColumnValue End) as [Name]

from

#data

Group By

GroupId

Only on SQL server 2005,

Code Snippet

Select * From

(Select GroupId,ColumnName,ColumnValue from #data) as Data

Pivot

(

Max(ColumnValue) For ColumnName in([MAC Address], [IP Address], [Name])

)

as PVT

|||

The one problem though is that you are still manually specifying column names, when I don't want to have to manually change my SQL each time I add a new column in the table... Is there no way of making this more dynamic?

Regards,

Justin

|||

Using the previous temp table data,

On SQL Server 2000/2005

Code Snippet

Declare @.PreparedQuery as Varchar(8000);

Declare @.SqlQuery as Varchar(8000);

Set @.PreparedQuery = 'Max(Case When[ColumnName] = ''?'' Then ColumnValue End) as [?]'

Set @.SqlQuery = 'Select GroupId'

Select @.SqlQuery = @.SqlQuery + ',' +Replace(@.PreparedQuery,'?',ColumnName)

From

(select Distinct ColumnName from #data) as Data

Exec (@.SqlQuery + 'from #data Group By GroupId')

On SQL Server 2005

Code Snippet

Declare @.PreparedQuery as Varchar(8000);

Declare @.SqlQuery as Varchar(8000);

Set @.PreparedQuery = '[?]'

Set @.SqlQuery =

'Select * From (Select GroupId,ColumnName,ColumnValue from #data) as Data

Pivot (Max(ColumnValue) For ColumnName in('

Select @.SqlQuery = @.SqlQuery + Replace(@.PreparedQuery,'?',ColumnName) + ','

From

(select Distinct ColumnName from #data) as Data

Set@.SqlQuery = Substring(@.SqlQuery,1,Len(@.SqlQuery)-1) + ')) as Pvt'

Exec (@.SqlQuery)

|||Works a charm, and is a lot less code than I originally thought it would be, thank you!