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
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?
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.
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 -
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 ;
16 People are following this question.
