display column into different rows

Question

guys,

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?

Answer 1

Try:

;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


My blog

Answer 2

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...

vt

 

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

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

Answer 4

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....

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9aa7c832-7f4b-4c08-87cf-7fe9cd09d080/#d700b018-93d1-456b-b4f7-32278f9cefd0

All participants here are experts in their own areas, and they have my respect.

Take it easy.....