May 16, 2011 at 11:11 am
Hello All,
I am kind of new to T-SQL and I have a situation here. I have the below shown table structure (there is no Sr.NO column in the actual table, I added that column)
Sr.No SkillWllfsh SkillVideo SkillCHSI SkillCHN SkillCDV SkillCCO SkillRpts
1 1 1 1 1 1 1 0
2 1 1 1 1 1 0 0
3 1 1 1 1 1 0 0
4 1 1 0 0 0 0 0
5 1 1 0 1 1 0 0
6 0 1 0 0 0 0 0
below is the code we use for each column:
SkillWllfsh w
SkillVideo v
SkillCHSI m
SkillCHN n
SkillCDV t
SkillCCO c
SkillRpts r
and belwo is the output that I am looking for:
Sr.No Skills
1 w,v,m,n,t,c
2 w,v,m,n,t
3 w,v,m,n,t
4 w,v
5 w,v,n,t
6 v
Please let me knwo the T-SQL to get the above output
Thanks in Advance
May 16, 2011 at 11:19 am
Please refer to the following article by Jeff Moden:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2011 at 11:43 am
I had to build your code set but you this will give you the results your looking for though you may want to store your data in a different relational format to make data transformations like this more efficient.
declare @Mytable as table(
SrNO int identity(1,1),
skillwllfsh int,
skillvideo int,
skillchsi int,
skillchn int,
skillcdv int,
skillcco int,
skillrpts int
)
insert into @Mytable (skillwllfsh, skillvideo, skillchsi, skillchn, skillcdv, skillcco, skillrpts) values
(1, 1, 1, 1, 1, 1, 0),
(1, 1, 1, 1, 1, 0, 0),
(1, 1, 1, 1, 1, 0, 0),
(1, 1, 0, 0, 0, 0, 0),
(1, 1, 0, 1, 1, 0, 0),
(0, 1, 0, 0, 0, 0, 0)
selectSrNO,
substring(isnull(case when skillwllfsh = 1 then 'w,' else '' end +
case when skillvideo = 1 then 'v,' else '' end +
case when skillchsi = 1 then 'm,' else '' end +
case when skillchn = 1 then 'n,' else '' end +
case when skillcdv = 1 then 't,' else '' end +
case when skillcco = 1 then 'c,' else '' end +
case when skillrpts = 1 then 'r,' else '' end, '_'),1,
len(isnull(case when skillwllfsh = 1 then 'w,' else '' end +
case when skillvideo = 1 then 'v,' else '' end +
case when skillchsi = 1 then 'm,' else '' end +
case when skillchn = 1 then 'n,' else '' end +
case when skillcdv = 1 then 't,' else '' end +
case when skillcco = 1 then 'c,' else '' end +
case when skillrpts = 1 then 'r,' else '' end, '_'))-1) as DesiredOutput
from @Mytable
May 16, 2011 at 11:49 am
Thank you so much SQL Experts, that would be a gr8 help 🙂
May 16, 2011 at 12:33 pm
Try this:
declare @Mytable as table(
SrNO int identity(1,1),
skillwllfsh int,
skillvideo int,
skillchsi int,
skillchn int,
skillcdv int,
skillcco int,
skillrpts int
)
insert into @Mytable (skillwllfsh, skillvideo, skillchsi, skillchn, skillcdv, skillcco, skillrpts) values
(1, 1, 1, 1, 1, 1, 0),
(1, 1, 1, 1, 1, 0, 0),
(1, 1, 1, 1, 1, 0, 0),
(1, 1, 0, 0, 0, 0, 0),
(1, 1, 0, 1, 1, 0, 0),
(0, 1, 0, 0, 0, 0, 0)
declare @codes table
( colname varchar(100) , description varchar(100) )
insert into @codes values
('SkillWllfsh','w' ),
('SkillVideo','v' ),
('SkillCHSI','m' ),
('SkillCHN','n' ),
('SkillCDV','t' ),
('SkillCCO','c' ),
('SkillRpts','r' )
; with unpivoted_data as
(
select *
from @Mytable unpivot_source
unpivot
( Vals for Cols IN ( skillwllfsh, skillvideo, skillchsi, skillchn, skillcdv, skillcco, skillrpts )) unpviot_hanlder
where Vals <> 0
),
codesandvalues as
(
select upvt.SrNO , cds.description
from unpivoted_data upvt
join @codes cds
on upvt.Cols = cds.colname
)
select srno,
STUFF ( ( SELECT ','+ description
FROM codesandvalues innertbl
WHERE innertbl.SrNO = outrtbl.SrNO
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
from codesandvalues outrtbl
group by outrtbl.SrNO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply