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
Best Answer
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;
