April 21, 2008 at 11:43 pm
Hi,
I have a table in the following format:
Site ID DateDescription DateValue
1 DateName1 Date
2 DateName1 Date
1 DateName2 Date
2 DateName2 Date
1 DateName3 Date
2 DateName3 Date
The number of sites will increase, and the number of different DateNames will also increase. The DateDescription (ie. DateName1) will only occur once for any particular siteID.
I would like to get this table in the following format:
Site ID DateName1 Date Name2 Date Name3
1 Date Date Date
2 Date Date Date
...........and so on
I would like it for reporting, but would also like it if the user can update the information from the final table format.
Any suggestions would be great.
Thanks
April 22, 2008 at 12:32 am
[font="Verdana"]
Declare @strSQL VarChar(Max)
declare @count int
set @count = 0
Select @count = @count + 1, @strSQL = (Case When @strSQL Is Null Then '''' + DateValue + ''' as DateName1' Else @strSQL + ', ' + '''' + DateValue + ''' as DateName' + convert(varchar, @count) End)
From {Table}
Select @strSQL = 'select ' + @strSQL
exec (@strSQL)
Try this and you will get an idea.
Mahesh[/font]
MH-09-AM-8694
April 22, 2008 at 12:53 am
Hi,
Thanks for your reply. The DateNames will all be different (ie. not just incremented by 1 in the name). I have used this notation in the example to explain the situation.
Thanks
April 22, 2008 at 3:05 am
Hi
You can use Pivot transformation in SSIS to achieve this
April 22, 2008 at 3:12 am
Use the PIVOT operator for this (not in SSIS) .
"Keep Trying"
April 22, 2008 at 4:53 am
I did further investigation on this. Here is my approach
select * from site
1WeekEnding052520082008-05-25 00:00:00.000
2WeekEnding052520082008-05-25 00:00:00.000
1WeekEnding051820082008-05-18 00:00:00.000
2WeekEnding051820082008-05-18 00:00:00.000
1WeekEnding051120082008-05-11 00:00:00.000
2WeekEnding051120082008-05-11 00:00:00.000
Use pivot to get the table in pivot format
select siteid,WeekEnding05252008,WeekEnding05182008,WeekEnding05112008
from
(select siteid,datedescription,datevalue from site
) as rf
pivot
( max(datevalue) for datedescription in (WeekEnding05252008,WeekEnding05182008,WeekEnding05112008)
) as p
Output is as follows:
SiteID WeekEnding05252008 WeekEnding05182008 WeekEnding05112008
12008-05-25 00:00:00.0002008-05-18 00:00:00.0002008-05-11 00:00:00.000
22008-05-25 00:00:00.0002008-05-18 00:00:00.0002008-05-11 00:00:00.000
Hope this helps you
April 22, 2008 at 6:24 am
[font="Verdana"]Yup, I think Bindu has suggested nice solution. Even she has exact o/p desired by the original poster.
Mahesh[/font]
MH-09-AM-8694
April 22, 2008 at 5:03 pm
Hi,
Thanks for all your replies. I have used the above code, in SQL Server Express 2005 and received an error message stating that PIVOT was not supported.
It did actually show the output table (at the bottom of the view design screen), so thanks, it did work.
Although, I could not edit the table. Do you have any suggestions on how to make the table editable?
And, is PIVOT not supported in SSExpress2005?
Thanks again.
April 22, 2008 at 11:01 pm
[font="Verdana"]
...is PIVOT not supported in SSExpress2005?
It might be the problem of Compatibility Level. Even I have faced the same problem with SQL Server 2K5. Check the Compatibility Level and change to 90, if its below 90.
For more information on Compatibility Level, refer:
http://msdn2.microsoft.com/en-us/library/ms178653.aspx
Mahesh
[/font]
MH-09-AM-8694
April 23, 2008 at 12:02 am
Pivot is supported in Express edition.
"Keep Trying"
April 23, 2008 at 12:19 am
Hi,
My compatability level is 90 which is SQL Server 2005, so this is not the issue.
Any ideas on how to get the Pivot to be editable?
Thanks
April 23, 2008 at 12:22 am
Hi
I guess its compatibility issue. That can be resolved by executing the stmt given below
EXEC sp_dbcmptlevel , 90;
Pass the database name on which you are trying to execute PIVOT.
Hope this works for you.
Regards
Bindu
April 23, 2008 at 12:37 am
Hi
I could find one solution. we can use CASE statement to modify the final pivot output
select siteid,WeekEnding05252008,WeekEnding05182008,
(case WeekEnding05112008
when '2008-05-11 00:00:00.000' then '2008-05-12 00:00:00.000'
else WeekEnding05112008
end) as WeekEnding05112008
from
(select siteid,datedescription,datevalue from site
) as rf
pivot
( max(datevalue) for datedescription in (WeekEnding05252008,WeekEnding05182008,WeekEnding05112008)
) as p
output of this as follows:
siteid WeekEnding05252008 WeekEnding05182008 WeekEnding05112008
12008-05-25 00:00:00.0002008-05-18 00:00:00.0002008-05-12 00:00:00.000
22008-05-25 00:00:00.0002008-05-18 00:00:00.0002008-05-12 00:00:00.000
I hope this is what was expected.
Regards
Bindu
April 23, 2008 at 5:54 pm
Thanks Bindu,
I have been able to get this output, but I would like to be able to edit the dates when in that view/output.
Do you have any suggestions on how to make it so that I can edit values in that pivot view?
Thanks
April 24, 2008 at 12:30 am
Hi
We can use case statement to edit the pivot output.
The actual data in the table is as follows:
siteiddatedescription datevalue
1WeekEnding052520082008-05-25 00:00:00.000
2WeekEnding052520082008-05-25 00:00:00.000
1WeekEnding051820082008-05-18 00:00:00.000
2 WeekEnding05182008 2008-05-18 00:00:00.000
1 WeekEnding05112008 2008-05-11 00:00:00.000
2 WeekEnding05112008 2008-05-11 00:00:00.000
when we execute the statement :
select siteid,WeekEnding05252008,WeekEnding05182008,WeekEnding05112008
from
(select siteid,datedescription,datevalue from site
) as rf
pivot
( max(datevalue) for datedescription in (WeekEnding05252008,WeekEnding05182008,WeekEnding05112008)
) as p
Output is as follows:
SiteID WeekEnding05252008 WeekEnding05182008 WeekEnding05112008
1 2008-05-25 00:00:00.000 2008-05-18 00:00:00.000 2008-05-11 00:00:00.000
2 2008-05-25 00:00:00.000 2008-05-18 00:00:00.000 2008-05-11 00:00:00.000
To alter the pivot output we can execute the following statement
select siteid,WeekEnding05252008,WeekEnding05182008,
(case WeekEnding05112008
when '2008-05-11 00:00:00.000' then '2008-05-12 00:00:00.000'
else WeekEnding05112008
end) as WeekEnding05112008
from
(select siteid,datedescription,datevalue from site
) as rf
pivot
( max(datevalue) for datedescription in (WeekEnding05252008,WeekEnding05182008,WeekEnding05112008)
) as p
Output is as follows:
SiteID WeekEnding05252008 WeekEnding05182008 WeekEnding05112008
1 2008-05-25 00:00:00.000 2008-05-18 00:00:00.000 2008-05-12 00:00:00.000
2 2008-05-25 00:00:00.000 2008-05-18 00:00:00.000 2008-05-12 00:00:00.000
Actual date for "WeekEnding05112008" is 2008-05-11 00:00:00.000, by using the case statement we modified this value to 2008-05-12 00:00:00.000
II) There is another solution also. We can svae this pivot output to a table and then we can update the table
select siteid,WeekEnding05252008,WeekEnding05182008,WeekEnding05112008
into sitepivot
from
(select siteid,datedescription,datevalue from site
) as rf
pivot
( max(datevalue) for datedescription in (WeekEnding05252008,WeekEnding05182008,WeekEnding05112008)
) as p
select * from sitepivot
Hope this helps you.
Regards
Bindu
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply