Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Pivot Or not Pivot ( or UnPivot)

Author  Topic 

Clages1
Yak Posting Veteran

69 Posts

Posted - 2014-12-17 : 16:48:15
Hi, i have a table like this

Id , Date, Patient, blood , Urine , Cholesterol
123 15/01/14 john 123 12 33
123 11/07/14 john 456 11 34
123 01/09/14 john 22 11 31
...
125 01/03/14 mary 25 12 31
125 01/11/14 mary 35 12 31

..
I am trying to use Unpivot to get the result like this
but Unsuccesfull


15/01/14 11/07/14 01/09/14
Blood 123 456 22
Urine 12 11 11
Cholesterol 33 34 31


all samples i am searching use (max, Min, avg) etc

the main problem is that Date is unknown, because the patient
could have a lot of exam

Any ideas?
tks for any help

att
Clages

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 20:22:20
If the values of date are unknown, you may need a dynamic sql solution. Here's you you do it:


create table #t (Id int, Date date, Patient varchar(20), blood int, Urine int, Cholesterol int)
insert into #t(ID, date, patient, blood, Cholesterol, Urine) values
(123 ,'2014-01-15','john', 123 ,12,33),
(123 ,'2014-07-11','john', 456 ,11,34),
(123 ,'2014-09-01','john', 22 ,11,31)

declare @cols nvarchar(max);
set @cols = stuff((
select ','+quotename(date)
from #t
order by date
for xml path('')
),1,1,'')
--select @cols

declare @sql nvarchar(max) = N'
select measure, ' + @cols +
' from (select ''blood'' as measure, date, blood from #t)src ' +
' pivot(sum(blood) for date in (' + @cols + '))pvt' +
' union all ' +
' select measure, ' + @cols +
' from (select ''urine'' as measure,date, Urine from #t)src ' +
' pivot(sum(Urine) for date in (' + @cols + '))pvt' +
' union all ' +
' select measure, ' + @cols +
' from (select ''chloresterol'' as measure,date, Cholesterol from #t)src ' +
' pivot(sum(Cholesterol) for date in (' + @cols + '))pvt'

print @sql
exec ( @sql)


Note that you need multiple pivots since you have multiple measures. Also, this may not run as fast as you like on large tables. YMMV
Go to Top of Page

Clages1
Yak Posting Veteran

69 Posts

Posted - 2014-12-18 : 06:11:26
Tks, that's it
now i will improve to my needs, and see how fast the query is
att
CLages
Go to Top of Page
   

- Advertisement -