SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 transact - SQL development
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Alex2011
Starting Member

18 Posts

Posted - 02/04/2013 :  14:24:50  Show Profile  Reply with Quote
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
6059 Posts

Posted - 02/04/2013 :  15:02:17  Show Profile  Reply with Quote
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 - 02/04/2013 :  15:45:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 02/04/2013 :  15:52:50  Show Profile  Reply with Quote
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 - 02/04/2013 :  16:05:22  Show Profile  Reply with Quote
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 - 02/04/2013 :  17:23:54  Show Profile  Reply with Quote
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 - 02/05/2013 :  09:19:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000