April 22, 2010 at 2:59 am
I'm not sure that using the PIVOT function is applicable here (i've tried to get it to work!) but i'm trying to get multiple 'Codes' into one column.
My stored procedure will currently bring back information in the following way.
declare @CurrentSPResult table(ID int,name varchar(50),unitscompleted varchar(50))
insert into @CurrentSPResult
select
50547 ,
'rupert',
'30%'
select * from @CurrentSPResult
i then want to add another column to this select statement called 'Units Used'.
This will need to look at another table and bring back a DISTINCT set of units (across Code1,Code2 & Code3 for each row) used for this person.
This table stores the information like so:
declare @CodeTable table(ID int,PersonID int,Code1 char(1),Code2 char(1),Code3 char(1))
insert into @codetable
select 1 ,50547 ,'O' ,'P' ,'S'
insert into @codetable
select 2,50547 ,'P','T','U'
insert into @CodeTable
select 3,50547,'R','U',''
insert into @CodeTable
select 4,50547,'','',''
insert into @CodeTable
select 5,50547,'T','P','O'
insert into @CodeTable
select 6,50547,'','','U'
insert into @CodeTable
select 7,50547,'','S',''
select * from @CodeTable
the final result of my SELECT statement should look something like this:
declare @CurrentSPResult table(ID int,name varchar(50),unitscompleted varchar(50),unitsused varchar(50))
insert into @CurrentSPResult
select
50547 ,
'rupert',
'30%' ,
'O,P,S,T,U'
select * from @CurrentSPResult
is this possible? do you require any more info? if so, just ask!
thanks.
April 22, 2010 at 3:40 am
This should do the trick for you:
declare @CurrentSPResult table(ID int,name varchar(50),unitscompleted varchar(50))
insert into @CurrentSPResult
select 50547, 'rupert', '30%'
declare @CodeTable table(ID int,PersonID int,Code1 char(1),Code2 char(1),Code3 char(1))
insert into @codetable
select 1 ,50547 ,'O' ,'P' ,'S'
insert into @codetable
select 2,50547 ,'P','T','U'
insert into @CodeTable
select 3,50547,'R','U',''
insert into @CodeTable
select 4,50547,'','',''
insert into @CodeTable
select 5,50547,'T','P','O'
insert into @CodeTable
select 6,50547,'','','U'
insert into @CodeTable
select 7,50547,'','S',''
select *, codes = STUFF((
select N',' + code AS [text()]
from (
select distinct code
from @CodeTable
unpivot( code for codeField in (Code1, Code2, Code3)) as u
where code <> ''
and PersonID = Person.ID
) as codes
order by code
for xml path(''))
,1,1, SPACE(0))
from @CurrentSPResult AS Person
-- Gianluca Sartori
April 22, 2010 at 4:20 am
oh wow!, thanks!
i had sort of found a way and was half way through implementing it but this way is much less code 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply