Special request

Transact-SQL
1

I have a table that contains data as follows:

mytable

col1, col2, col3, col4
10,2,10,50
20,6,30,20
6,15,14,80

in case the values are repeated I display a time sele

example
select col1, col2, col3, col4 from my table

This should give

10,20,50
20,6,30
6,15,14,80

2

This should give

10,2,50
20,6,30
6,15,14,80

3

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]
4

Thank you khtan

it works,

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

5

another alternative is to use UNPIVOT and PIVOT

6

can i have a help,

PIVOT: I did not understand the online help

7

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

SELECT
    ca1.id,
    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
CROSS APPLY (
    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