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 2012 Forums
 Transact-SQL (2012)
 transact - SQL development

Author  Topic 

Alex2011
Starting Member

18 Posts

Posted - 2013-02-04 : 14:24:50
HELLO All, can anybody help me on how to query the following table to get a result table shown below? Appreciate any suggestions!

Original table:
ID Certification State Certification Held
1 Cert 1 CA
1 Cert 1 OR
1 Cert 2 AZ
1 Cert 2 CA
1 Cert 2 NV
2 ..... ..
2 ..... ..
......

Result:
ID Certifications
1 Cert 1 held in CA, OR; Cert 2 held in AZ, CA, NV
2 ............

Thanks,
Alex

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-04 : 15:02:17
Here's one (ugly) way:

;with originalTable (ID, Certification, StateCertificationHeld)
as (
select 1, 'Cert 1', 'CA' union all
select 1, 'Cert 1', 'OR' union all
select 1, 'Cert 2', 'AZ' union all
select 1, 'Cert 2', 'CA' union all
select 1, 'Cert 2', 'NV' union all
select 2, 'Cert 1', 'AZ' union all
select 2, 'Cert 2', 'CA' union all
select 2, 'Cert 2', 'NV'
)

select id, stuff(max(certs), 1,1,'') certifications
from originalTable ot
cross apply (
select '; ' + certification + ' held in ' + stuff(ca2.sts,1,1,'')
from originalTable ot1
cross apply (
select ',' + StateCertificationHeld
from originalTable
where id = ot1.id
and Certification = ot1.Certification
for xml path('')
) ca2 (sts)
where id = ot.id
group by '; ' + certification + ' held in ' + stuff(ca2.sts,1,1,'')
for xml path('')
) ca1 (certs)
group by id


OUTPUT:
id certifications
----------- -------------------------------------------------
1 Cert 1 held in CA,OR; Cert 2 held in AZ,CA,NV
2 Cert 1 held in AZ; Cert 2 held in CA,NV


Be One with the Optimizer
TG
Go to Top of Page

Alex2011
Starting Member

18 Posts

Posted - 2013-02-04 : 15:45:33
WOW - The codes work perfectly for me.

Thanks for the quick response! Every time I got exactly what I need from here.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-04 : 15:52:50
Cool - I hope you're learning too so pretty soon you'll be answering other people's questions

Be One with the Optimizer
TG
Go to Top of Page

Alex2011
Starting Member

18 Posts

Posted - 2013-02-04 : 16:05:22
yep, for sure I need improve myself on SQL development although I use it occasionally for some data-driven web app development. thanks again for your help!
Go to Top of Page

Alex2011
Starting Member

18 Posts

Posted - 2013-02-04 : 17:23:54
TG, I was stuck on another query - I appreciate if you can help me on it.

Original table:
ID Column1, Column2, Column3, Column4
1 Little Expert Expert Proficient
2 Expert Expert Proficient Little

Result:
ID Expertise
1 Little knowledge on Column1; Proficient at Column4; Expert at Column2, Column3
2 Little knowledge on Column4; Proficient at Column3; Expert at Column1, Column2
Go to Top of Page

Alex2011
Starting Member

18 Posts

Posted - 2013-02-05 : 09:19:42
ok, here are the codes.

;With cteUnpivot As
(Select ID, ColName, Knowledge, Row_Number() Over (Partition By ID, Knowledge Order By ColName) As rn
From #Test t
Unpivot
(Knowledge For ColName In (Column1, Column2, Column3, Column4)) As Unpvt),
cteConcatenate As
(Select Distinct c1.ID, Stuff((Select '; ' + Case When c2.rn = 1 Then '1' + c2.Knowledge Else '2' End + c2.ColName
From cteUnpivot c2
Where c1.ID = c2.ID
Order By Case When c2.Knowledge = 'L' Then 1 When c2.Knowledge = 'P' Then 2 Else 3 End, c2.ColName
For XML Path('')), 1, 2, '') As Expertise
From cteUnpivot c1)
Select ID, Replace(Replace(Replace(Replace(Expertise,
'1L', 'Little Knowledge on '),
'1P', 'Proficient at '),
'1E', 'Excellent at '),
'; 2', ', ') As Expertise
From cteConcatenate
Order By ID;
Go to Top of Page
   

- Advertisement -