CODEWITHSUNDEEP
sqlsql-server
user757321
user757321

Reputation: 341

How to write a complex master-detail query SQL Server

I need some help getting a SQL query running the way I need. I know you SQL gurus will be laughing at this, but I really don't know how to do this :-( I have a table in the MS-SQL database with the following data

A   B       C
10  1001    1001001
10  1001    1001002
10  1001    1001003
10  1001    1001004
10  1002    1002001
10  1002    1002002
20  2001    2001001
20  2001    2001002
20  2002    2002001

I want the following Result

10
1001
1001001
1001002
1001003
1001004
1002
1002001
1002002
20
2001
2001001
2001002
2002
2002001

Upvotes: 0

Views: 469

Answers (2)

user12599189
user12599189

Reputation: 11

WITH items AS (
  SELECT CAST(A AS NVARCHAR) AS item FROM Table
  UNION
  SELECT CAST(B AS NVARCHAR) FROM Table
  UNION
  SELECT CAST(C AS NVARCHAR) FROM Table
)
SELECT *
  FROM items
 ORDER BY item

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270763

I think you can do what you want using grouping sets and order by:

select coalesce(c, b, a)
from t
group by grouping sets ( (a), (a, b), (a, b, c) )
order by a,
         (case when b is null then 1 else 2 end),
         b,
         (case when c is null then 1 else 2 end),
         c;

Here is a db<>fiddle.

Given the nature of your data, you can simplify the order by to:

order by a, coalesce(b, a), coalesce(c, b, a)

Upvotes: 3

Related Questions