CODEWITHSUNDEEP
sqlsql-servert-sql
FrenkyB
FrenkyB

Reputation: 7197

Select multiple rows depending on colum values

I have a table like this:

+----------+----------+----------+----------+----------+
| COLI_KEY | COLI_DAY | COLI_WEE | COLI_MON | COLI_YEA |
+----------+----------+----------+----------+----------+
|        1 | 1        | NULL     |        1 | NULL     |
|        2 | NULL     | NULL     |        1 | 1        |
|        3 | 1        | 1        |        1 | 1        |
+----------+----------+----------+----------+----------+

What I would like to do is get each row for each value in COLI_DAY, COLI_WEE, COLI_MON, COLI_YEA. So basically I need a SELECT statement which will give me desired result (only the one column is marked in each row - other columns are null):

+----------+----------+----------+----------+----------+
| COLI_KEY | COLI_DAY | COLI_WEE | COLI_MON | COLI_YEA |
+----------+----------+----------+----------+----------+
|        1 | 1        | null     | null     | null     |
|        1 | null     | null     | 1        | null     |
|        2 | null     | null     | 1        | null     |
|        2 | null     | null     | null     | 1        |
|        3 | 1        | null     | null     | null     |
|        3 | null     | 1        | null     | null     |
|        3 | null     | null     | 1        | null     |
|        3 | null     | null     | null     | 1        |
+----------+----------+----------+----------+----------+

Script for creating the table:

declare @CACOLI table
(
    COLI_KEY INT,
    COLI_DAY CHAR(1),
    COLI_WEE CHAR(1),
    COLI_MON CHAR(1),
    COLI_YEA CHAR(1)
)

INSERT INTO @CACOLI (COLI_KEY, COLI_DAY, COLI_WEE, COLI_MON, COLI_YEA)
VALUES (1, '1', NULL, '1', NULL),
       (2, NULL, NULL, '1', '1'),
       (3, '1', '1', '1', '1')

Upvotes: 1

Views: 96

Answers (3)

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

This is a straight forward approach where I select the outputs for each columns where it's one while keeping others NULL and union them all.

SELECT
    COLI_KEY, COLI_DAY, NULL AS COLI_WEE, NULL AS COLI_MON, NULL AS COLI_YEA
WHERE 
    COLI_DAY = 1

UNION

SELECT 
    COLI_KEY, NULL AS COLI_DAY, COLI_WEE, NULL AS COLI_MON, NULL AS COLI_YEA
WHERE 
    COLI_WEE = 1

UNION

SELECT
    COLI_KEY, NULL AS COLI_DAY, NULL AS COLI_WEE, COLI_MON, NULL AS COLI_YEA
WHERE 
    COLI_MON = 1

UNION

SELECT
    COLI_KEY, NULL AS COLI_DAY, NULL AS COLI_WEE, NULL AS COLI_MON, COLI_YEA
WHERE 
    COLI_YEA = 1

Upvotes: 2

PSK
PSK

Reputation: 17943

You can also achieve this using UNPIVOT and PIVOT like following.

;with cte1 
     as (select *,row_number() over(order by (select 1)) rn 
         from   @table s 
                unpivot( [y] 
                        for [x] IN (coli_day,coli_wee,coli_mon,coli_yea) ) u) 
select coli_key,coli_day,coli_wee,coli_mon,coli_yea 
from   cte1 
       pivot(max(y) 
            for x in(coli_day,coli_wee,coli_mon,coli_yea)) pvt

Online Demo

Note: Change @table to your actual table name.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

We can try using a calendar table here, which represents all possible tuples you might see in the expected output.

WITH all_keys AS (
    SELECT 1 AS COLI_KEY UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
),
data AS (
    SELECT 1 AS COLI_DAY, NULL AS COLI_WEE, NULL AS COLI_MON,
        NULL AS COLI_YEA UNION ALL
    SELECT NULL, 1, NULL, NULL UNION ALL
    SELECT NULL, NULL, 1, NULL UNION ALL
    SELECT NULL, NULL, NULL, 1
)

SELECT
    a.COLI_KEY,
    d.COLI_DAY,
    d.COLI_WEE,
    d.COLI_MON,
    d.COLI_YEA
FROM all_keys a
CROSS JOIN data d
INNER JOIN yourTable t
    ON t.COLI_KEY = a.COLI_KEY AND
       (t.COLI_DAY = d.COLI_DAY OR
        t.COLI_WEE = d.COLI_WEE OR
        t.COLI_MON = d.COLI_MON OR
        t.COLI_YEA = d.COLI_YEA);

Demo

Upvotes: 2

Related Questions