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.
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 Held1 Cert 1 CA1 Cert 1 OR1 Cert 2 AZ1 Cert 2 CA1 Cert 2 NV2 ..... ..2 ..... ........Result:ID Certifications1 Cert 1 held in CA, OR; Cert 2 held in AZ, CA, NV2 ............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,'') certificationsfrom originalTable otcross 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 idOUTPUT:id certifications----------- -------------------------------------------------1 Cert 1 held in CA,OR; Cert 2 held in AZ,CA,NV2 Cert 1 held in AZ; Cert 2 held in CA,NV Be One with the OptimizerTG |
|
|
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. |
|
|
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 OptimizerTG |
|
|
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! |
|
|
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, Column41 Little Expert Expert Proficient2 Expert Expert Proficient LittleResult:ID Expertise1 Little knowledge on Column1; Proficient at Column4; Expert at Column2, Column32 Little knowledge on Column4; Proficient at Column3; Expert at Column1, Column2 |
|
|
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 rnFrom #Test tUnpivot(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 ExpertiseFrom cteUnpivot c1)Select ID, Replace(Replace(Replace(Replace(Expertise, '1L', 'Little Knowledge on '), '1P', 'Proficient at '), '1E', 'Excellent at '), '; 2', ', ') As ExpertiseFrom cteConcatenateOrder By ID; |
|
|
|
|
|
|
|