Tuesday, January 08, 2013 10:13 PM
i was working on some other thread. can some one quickly get this sorted.
here is the input:
i wanted to produce the out put like this: converting single row in to three column. Do not advise me to use SSIS. SQL query?
Tuesday, January 08, 2013 10:36 PMModerator
;with cte as (select Id, Input, dense_rank() over (partition by ID %6 order by Id) as GroupId from InputTable) select max(case when Input like 'Device%' then Input end) as Device,
max(case when Input like 'FreeSpace%' then Input end) as FreeSpace, max(case when Input like 'Size%' then Input end) as Size from cte GROUP BY GroupID
The Idea of GroupID calculated column is to keep something to group information for particular device together. If you have such column already which you didn't show use it instead of that GroupId column.
For every expert, there is an equal and opposite expert. - Becker's Law
Tuesday, January 08, 2013 10:40 PM
We are not your servants. All the users of this forum are putting a great effort and time to help each other. One must understand that..
show some respect to others effort...
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
Wednesday, January 09, 2013 2:26 AM
create table #tb (id int,value varchar(1000)) insert into #tb select 3,'DeviceID :C:' union all select 4,'DriveType :3' union all select 5,'ProviderName :' union all select 6,'FreeSpace :65539960832' union all select 7,'Size :14575851360' union all select 8,'VolumnName :' union all select 10,'DeviceID :D:' union all select 11,'DriveType :3' union all select 12,'ProviderName :' union all select 13,'FreeSpace :96388603904' union all select 14,'Size :298863030272' union all select 15,'VolumnName :Logs' union all select 17,'DeviceID :E:' union all select 18,'DriveType :3' union all select 19,'ProviderName :' union all select 20,'FreeSpace :391555645440' union all select 21,'Size :515392925692' union all select 22,'VolumnName :Database' ;with cte as ( select (row_number()over(order by id)-1)/6 as type,* from #tb ) select a.value,b.value,c.value from cte a inner join cte b on a.type=b.type inner join cte c on a.type=c.type where a.value like 'DeviceID%' and b.value like 'FreeSpace%' and c.value like 'Size%'
I suggest that you can provide the sample data instead of snapshot
Wednesday, January 09, 2013 11:07 PM
Thanks Naomi & Murali.
i found solution soon after this post and deleted this thread. Not sure what happend.
i soon work on your CTE query.
I know this is volunteering stuff and so am i ? As i said, i was working on other thread.
Check this out....
All participants here are experts in their own areas, and they have my respect.
Take it easy.....