SQLTeam.com | Weblogs | Forums

Special request


I have a table that contains data as follows:


col1, col2, col3, col4

in case the values are repeated I display a time sele

select col1, col2, col3, col4 from my table

This should give



This should give



this is good enough for you ?

select col1, 
       col2 = case when col1 <> col2 then col2 end, 
       col3 = case when col1 <> col3 and col2 <> col3 then col3 end, 
       col4 = case when col1 <> col4 and col2 <> col4 and col3 <> col4 then col4 end
from   [my table]


Thank you khtan

it works,

but my table has more than 4 columns, so it's a bit more complicated to write


another alternative is to use UNPIVOT and PIVOT


can i have a help,

PIVOT: I did not understand the online help


I assume you have some type of id in the main row. Otherwise, it's difficult to return the values in the same order in which they appear in the original columns.

drop table mytable
create table mytable (
    id int,
    col1 int,
    col2 int,
    col3 int,
    col4 int
insert into mytable(id, col1, col2, col3, col4) select 101, 10,2,10,50
insert into mytable(id, col1, col2, col3, col4) select 102, 20,6,30,20
insert into mytable(id, col1, col2, col3, col4) select 103, 6,15,14,80

    MAX(CASE WHEN ca1.row_num = 01 THEN ca1.col END) AS col1,
    MAX(CASE WHEN ca1.row_num = 02 THEN ca1.col END) AS col2,
    MAX(CASE WHEN ca1.row_num = 03 THEN ca1.col END) AS col3,
    MAX(CASE WHEN ca1.row_num = 04 THEN ca1.col END) AS col4
    --...and so on for additional columns
FROM mytable
    SELECT id, col, ROW_NUMBER() OVER(ORDER BY col#) AS row_num
    FROM (
        SELECT all_cols.id, all_cols.col, all_cols.col#, 
            ROW_NUMBER() OVER(PARTITION BY all_cols.col ORDER BY all_cols.col#) AS row_num
        FROM (
            VALUES(id, 01, col1),(id, 02, col2),(id, 03, col3),(id, 04, col4) --,...and so on for additional columns
        ) AS all_cols(id, col#, col)
    ) AS derived
    WHERE row_num = 1
) AS ca1
GROUP BY ca1.id