x

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

sample.png (6.0 kB)
more ▼

asked Feb 13 at 02:16 AM in Default

avatar image

marassh
21 2

Do you only have those nine columns?

Feb 13 at 12:00 PM ThomasRushton ♦♦

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

Feb 13 at 12:08 PM marassh
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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;
more ▼

answered Feb 13 at 12:21 PM

avatar image

ThomasRushton ♦♦
42.2k 20 57 53

+1. Very elegant!

Feb 13 at 03:55 PM Usman Butt

Thank you, this is exactly what I am looking for. Thanks again

Feb 13 at 04:38 PM marassh
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x118
x2

asked: Feb 13 at 02:16 AM

Seen: 41 times

Last Updated: Feb 13 at 04:38 PM

Copyright 2018 Redgate Software. Privacy Policy