question

zillabaug avatar image
zillabaug asked

I NEED HELP WRITING T SQL

I need help or suggestions on how to turn the table below lvl state code A WI 4045 B WI 4045 E WI 4045 F WI 4045 I WI 4045 M WI 4045 P WI 4045 R WI 4045 T WI 4045 Into Program Code State A E M R T 4053 WI - X - - X ![Does anyone knows how to do it? I was think about using a cursor but I don't know how to construct the SQL.][1] please see attached image for clarity [1]: /storage/temp/2240-table.jpg
querytsqlcursor
table.jpg (38.0 KiB)
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OK, I MIGHT BE ABLE TO HELP Where do the X's come from? Also, why does only a selection of the letters become columns? Is this constant?
1 Like 1 ·
the X's are just a check mark to indicate that the program code is E accredited in the state of wisconsin
0 Likes 0 ·
But there isn't anything I can see in your data that would tell me this? Am I missing something? If you can tell me what the logical rules are for assigning something with an X I should be able to write you a query to convert the data.
0 Likes 0 ·
That data is in the reg_lvl column in the table
0 Likes 0 ·
essentially, the logic is the code will 1. create some sort of temp table 7 columns (ProgramCode,State,A,E,M,R,T) 2. Parse it each row in the first table with reg_lvl,state_cd,site_cd and insert X in the respective A,E,M,R,T if reg_level exist and state_cd=4045 3. else insert -
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
I believe a PIVOT should work nicely here and avoid a cursor or any other looping. I don't have access to my home system to test this right now, but please try this and let me know if it works. If you are using a version of SQL Server older than 2012, you will need to use CASE instead of the IIF function. SELECT pvt.[Program Code], pvt.[State] , A = IIF(pvt.A is not null, 'X', '-') , E = IIF(pvt.E is not null, 'X', '-') , M = IIF(pvt.M is not null, 'X', '-') , R = IIF(pvt.R is not null, 'X', '-') , T = IIF(pvt.T is not null, 'X', '-') FROM ( SELECT mt.reg_lvl , [State] = mt.state_cd , [Program Code] = mt.site_cd FROM mytable mt ) t PIVOT ( MAX(site_cd) FOR reg_lvl IN ([A], [E], [M], [R], [T]) ) pvt ;
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot Tom. It worked.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.