| Author |
 Topic  |
|
|
Alex2011
Starting Member
18 Posts |
 Posted - 02/04/2013 : 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
Flowing Fount of Yak Knowledge
USA
5501 Posts |
Posted - 02/04/2013 : 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 |
 |
|
|
Alex2011
Starting Member
18 Posts |
Posted - 02/04/2013 : 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. |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5501 Posts |
Posted - 02/04/2013 : 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 |
|
|
|
Alex2011
Starting Member
18 Posts |
Posted - 02/04/2013 : 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! |
|
|
|
Alex2011
Starting Member
18 Posts |
Posted - 02/04/2013 : 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 |
|
|
|
Alex2011
Starting Member
18 Posts |
Posted - 02/05/2013 : 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; |
|
|
| |
Topic |
|
|
|
transact - SQL development
