Posted by jsfromynr on 12/23/05 13:15

Hi There,

Try this It might help you.
Create View vwtmpData1
As
Select 1 DeviceID ,'color' FieldName,'red ' FieldValue
Union All
Select 1,'shape','square'
Union All
Select 1,'Weight(kg)','2.0'
Union All
Select 2,'shape','round'
Union All
Select 2,'Weight(kg)','1.5'
Union All
Select 3,'color','blue'
Union All
Select 3,'shape','oval'
Union All
Select 3,'Weight(kg)','1.0'


Go



Declare @sql varchar(2000)
Set @sql = 'Select DeviceID '
Select @sql = @sql + ', Max( Case When FieldName = '''+FieldName +'''
Then FieldValue End) As [' + FieldName +']' + char(13) + char(10)
>From vwTmpData1 Group By FieldName
Set @sql = @sql + ' From vwTmpData1 Group By DeviceId'
Print @sql
Exec(@sql)

All said and done !! Still knowing the number of columns is good
insight in the bussiness needs of the client . What you are trying to
acheive is EAV (Entity Attribute Value), which is not so good. Just
think how much time it will waste to write a complex query.

With Warm regards
Jatinder Singh

• Next in forum: Stuck with sorting columns
• Prev in forum: Re: Export SPs?
• Thread view: Re: dynamically trasnpose rows into columns

[Reply to this message]