asked
Sort or rearange column data in SQL query -coalesce alternative
Hello, I am new to SQL and learning each day. Please I need help figuring out a situation. I am querying data in an environment where I can only do select statements. I am trying to query from a table where the data is dispersed but i want the query to move the data up null column just as coalesce will but without maintaining the non-null values as shown below. Please any help will be greatly appreciated. Thank you ![alt text][1] [1]: /storage/temp/4500-sample.png
Thank you for the response. No I have upto 18 columns that I will normally query. I figured If I am able to get help on the 9, then I can work my way up to the 18. As stated, I only have permission to do select statements on the database. Thanks again
Here's a non-dynamic query that might do what you want: /* Just setting up a temporary table and some data for testing */ DECLARE @ProdData TABLE ( Product_ID INT, Reg1 VARCHAR(20), Reg2 VARCHAR(20), Reg3 VARCHAR(20), Reg4 VARCHAR(20), Reg5 VARCHAR(20), Reg6 VARCHAR(20), Reg7 VARCHAR(20), Reg8 VARCHAR(20), Reg9 VARCHAR(20) ); INSERT INTO @ProdData (Product_ID, Reg1, Reg3, Reg6, Reg7, Reg9) VALUES (123456, '3A', '8u', '7y', 'P9', 'yu'); INSERT INTO @ProdData (Product_ID, Reg2, Reg4, Reg8) VALUES (987, 'Num2','Num4','Num8'); SELECT * FROM @ProdData; /* And here's where we do stuff. */ WITH Unpivoted AS (SELECT Product_ID, Reg, Val, ROW_NUMBER() OVER (PARTITION BY Product_ID ORDER BY Reg) AS RowNum FROM ( SELECT Product_ID, Reg1, Reg2, Reg3, Reg4, Reg5, Reg6, Reg7, Reg8, Reg9 FROM @ProdData ) p UNPIVOT(Val FOR Reg IN(Reg1, Reg2, Reg3, Reg4, Reg5, Reg6, Reg7, Reg8, Reg9)) AS unp) SELECT Product_ID, MAX(CASE WHEN RowNum = 1 THEN Val ELSE NULL END) AS Reg1, MAX(CASE WHEN RowNum = 2 THEN Val ELSE NULL END) AS Reg2, MAX(CASE WHEN RowNum = 3 THEN Val ELSE NULL END) AS Reg3, MAX(CASE WHEN RowNum = 4 THEN Val ELSE NULL END) AS Reg4, MAX(CASE WHEN RowNum = 5 THEN Val ELSE NULL END) AS Reg5, MAX(CASE WHEN RowNum = 6 THEN Val ELSE NULL END) AS Reg6, MAX(CASE WHEN RowNum = 7 THEN Val ELSE NULL END) AS Reg7, MAX(CASE WHEN RowNum = 8 THEN Val ELSE NULL END) AS Reg8, MAX(CASE WHEN RowNum = 9 THEN Val ELSE NULL END) AS Reg9 FROM Unpivoted Group by Product_ID;
21 People are following this question.
