I have columns something like this:
col1 | col2 | col3 | col4 | col5 | col6 |
-----+------+------+------+------+------+
a | b | c | a | c | c
I am trying to get unique values in the column it self PER row.
So ideally, I want a,b,c to be returned
I tried doing PIVOT and applying a DISTINCT but that doesn't go well as there are other columns that I couldn't show in the question.
So is there another way that this could be obtained?
Thanks in advance
Is this what you are looking for ..?
IF OBJECT_ID('tempdb..#Pivot_data')IS NOT NULL
DROP TABLE #Pivot_data
IF OBJECT_ID('tempdb..#tab')IS NOT NULL
DROP TABLE #tab
select * into #tab from
(select 'a'AS COL1,'b'AS COL2,'c'AS COL3,'a'AS COL4,'c'AS COL5,'c' COL6)AS A
DECLARE @Columns nvarchar(max) ,@QUERY NVARCHAR(MAX)
;WITH CTE AS (
SELECT COLUMNSS,COL_VALUES,ROW_NUMBER()OVER(PARTITION BY COL_VALUES ORDER BY (SELECT 1))RN FROM (
SELECT * FROM #tab
)AS A
UNPIVOT(COL_VALUES FOR COLUMNSS IN([col1],[col2],[col3],[col4],[col5],[col6])) AS B
)
,FINAL_Result as (select COLUMNSS,COL_VALUES from CTE where RN=1)
SELECT * INTO #Pivot_data FROM FINAL_Result
SET @Columns= (SELECT STUFF((SELECT ',['+COLUMNSS+']' FROM #Pivot_data FOR XML PATH('')),1,1,''))
SET @QUERY=N'SELECT * FROM (
SELECT * FROM #Pivot_data
) AS A
PIVOT (MAX(COL_VALUES)FOR COLUMNSS IN('+@Columns+'))
AS B'
PRINT @QUERY
EXEC (@QUERY)
Logic : From the given table i did unpivot and generated a Row_number() based on the column values and considered only which are Row_num=1 i.e Distinct columns values . and finally, i Pivoted the resultant data .
It seems to be CROSS APPLY would be work here
select a.Col from table t
cross apply (
values (t.Col1), (t.Col2), (t.Col3),
(t.Col4), (t.Col5), (t.Col6)
)a(Col)
group by a.Col
AND, do the PIVOT with your own way
Try this code
IF OBJECT_ID('tempdb..#Temptab')IS NOT NULL
DROP TABLE #Temptab
;With cte(col1 , col2 , col3 , col4 , col5 , col6 )
AS
(
SELECT 'a' , 'b' , 'c' , 'a' , 'c' , 'c'
)
SELECT DISTINCT AllColumn
INTO #Temptab FROM cte
CROSS APPLY ( VALUES (col1),( col2) , (col3) , (col4) , (col5) , (col6)
) AS A (AllColumn)
DECLARE @SqlQuery nvarchar(max)
,@Sql nvarchar(max)
SELECT @SqlQuery='SELECT DISTINCT '+STUFF((SELECT ', '+ReqColumn FROM
(
SELECT ''''+AllColumn +'''' +' AS Col'+ CAST(Seq AS VARCHAR(2)) As ReqColumn
FROm
(
SELECT ROW_NUMBER()OVER(Order by AllColumn) AS Seq,AllColumn FROM #Temptab
)dt
)dte FOR XML PATH ('')),1,1,'') +' From #Temptab'
PRINT @SqlQuery
EXEC(@SqlQuery)
Result
Col1 Col2 Col3
----------------------
a b c
You can first union all columns to get unique values and then use the GROUP_CONCAT() function in MySql or if your database is Oracle then use list_agg() function:
Please try the below SQL:
select GROUP_CONCAT(col1 SEPARATOR ', ') from (
select '1' as 'serial', col1 from pivot
union
select '1' as 'serial',col2 from pivot
union
select '1' as 'serial',col3 from pivot
union
select '1' as 'serial',col4 from pivot
union
select '1' as 'serial',col5 from pivot
union
select '1' as 'serial' ,col6 from pivot
)derived
GROUP BY serial;
Output:
Note : I have make the 'serial' column in my query to do the group by. You can use the your identifier field in group by clause if you have any.
GROUP