February 25, 2010 at 2:48 am
Hi All,
I have two tables Table1 and Table2.
Table1 :
Col1 col2 col3
1 A A1
2 B A2
1 B A1
1 C A3
3 A A4
I need to get all the distinct values from each of these columns and insert it into Table2.
That is my Table2 should llook lie this after insertion ,
Table2
Col1 Col2 Col3
1 A A1
2 B A2
3 C A3
NULL NULL A4
How do I do this ? I used union all with a seperate select statement for each column but the result is given in 1 column...which is not my desired result.
the select query I used is
select distinct Col1 from Table1
Union All
select distinct Col2 from Table1
Union All
select distinct Col3 from Table1
With Regards,
Anu..;-):hehe:
February 25, 2010 at 3:40 am
Here you go.
; WITH Table1
AS
(
SELECT1 AS Col1, 'A' AS Col2, 'A1' AS Col3 UNION ALL
SELECT2, 'B', 'A2' UNION ALL
SELECT1, 'B', 'A1' UNION ALL
SELECT1, 'C', 'A3' UNION ALL
SELECT3, 'A', 'A4'
)
SELECTT1.Col1, T2.Col2, T3.Col3
FROM(
SELECTROW_NUMBER() OVER( ORDER BY Col1 ) AS RowNumber, Col1
FROM(
SELECT DISTINCT Col1
FROM Table1
) T
) T1
FULL JOIN
(
SELECTROW_NUMBER() OVER( ORDER BY Col2 ) AS RowNumber, Col2
FROM(
SELECT DISTINCT Col2
FROM Table1
) T
) T2 ON T1.RowNumber = T2.RowNumber
FULL JOIN
(
SELECTROW_NUMBER() OVER( ORDER BY Col3 ) AS RowNumber, Col3
FROM(
SELECT DISTINCT Col3
FROM Table1
) T
) T3 ON T2.RowNumber = T3.RowNumber
--Ramesh
February 25, 2010 at 4:00 am
Hi ramesh sir,
I am your big fan...
and i know you...
You are working with infoton right...
Vaibhav
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
February 25, 2010 at 4:11 am
Another way
;WITH Table1 (Col1,Col2,Col3)AS(
SELECT 1, 'A', 'A1'
UNION ALL
SELECT 2, 'B', 'A2'
UNION ALL
SELECT 1, 'B', 'A1'
UNION ALL
SELECT 1, 'C', 'A3'
UNION ALL
SELECT 3, 'A', 'A4'
), cteDistinct AS(
SELECT Col1 = Col1,
Col1DR = DENSE_RANK() OVER(ORDER BY COL1),
Col2 = Col2,
Col2DR = DENSE_RANK() OVER(ORDER BY COL2),
Col3 = Col3,
Col3DR = DENSE_RANK() OVER(ORDER BY COL3)
FROM Table1
)
SELECT Col1 = c1.Col1,
Col2 = c2.Col2,
Col3 = c3.Col3
FROM (SELECT DISTINCT
Col1DR = Col1DR,
Col1 = Col1
FROM cteDistinct
) c1 FULL OUTER JOIN
(SELECT DISTINCT
Col2DR = Col2DR,
Col2 = Col2
FROM cteDistinct
) c2
ON c1.Col1DR = c2.Col2DR
FULL OUTER JOIN
(SELECT DISTINCT
Col3DR = Col3DR,
Col3 = Col3
FROM cteDistinct
) c3
ON c1.Col1DR = c3.Col3DR
February 25, 2010 at 4:13 am
Hi anuhya.mudumba
Try this code
; WITH Table1
AS
(
SELECT 1 AS Col1, 'A' AS Col2, 'A1' AS Col3 UNION ALL
SELECT 2, 'B', 'A2' UNION ALL
SELECT 1, 'B', 'A1' UNION ALL
SELECT 1, 'C', 'A3' UNION ALL
SELECT 3, 'A', 'A4'
)
select max(col1) as col1,max(col2) as col2,max(col3) as col3
from
(
select distinct col1, null as col2, null as col3,dense_rank() over (order by col1) as rnum
from table1
union all
select distinct null,col2, null,dense_rank() over (order by col2)
from table1
union all
select distinct null,null,col3,dense_rank() over (order by col3)
from table1
)T
group by rnum
--Divya
February 25, 2010 at 4:21 am
Even another way (after reading Divya Agrawal POST)
;WITH Table1 (Col1,Col2,Col3)AS(
SELECT 1, 'A', 'A1'
UNION ALL
SELECT 2, 'B', 'A2'
UNION ALL
SELECT 1, 'B', 'A1'
UNION ALL
SELECT 1, 'C', 'A3'
UNION ALL
SELECT 3, 'A', 'A4'
), cteDistinct AS(
SELECT Col1 = Col1,
Col1DR = DENSE_RANK() OVER(ORDER BY COL1),
Col2 = Col2,
Col2DR = DENSE_RANK() OVER(ORDER BY COL2),
Col3 = Col3,
Col3DR = DENSE_RANK() OVER(ORDER BY COL3)
FROM Table1
)
SELECT Col1 = MAX(c1.Col1),
Col2 = MAX(c2.Col2),
Col3 = MAX(c3.Col3)
FROM cteDistinct c1 FULL OUTER JOIN
cteDistinct c2
ON c1.Col1DR = c2.Col2DR
FULL OUTER JOIN
cteDistinct c3
ON c1.Col1DR = c3.Col3DR
GROUP BY COALESCE(c1.Col1DR,c2.col2DR, c3.Col3DR)
February 25, 2010 at 4:48 am
vaibhav.tiwari (2/25/2010)
Hi ramesh sir,I am your big fan...
and i know you...
You are working with infoton right...
Vaibhav
I am wondering how you know me, though I don't know you in anyways. BTW, I left Infoton 2 weeks ago:)
--Ramesh
February 25, 2010 at 4:52 am
Ramesh sir,
I was also working with in infoton vile parle.
I also left infoton 1 month before.
I listent a lot about you...
and most of the procedures of paysmart was written by you.
can i get your personal email id or contact number please.
Regards,
Vaibhav
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
February 25, 2010 at 5:15 am
Hey All.....thank you so much for your replies.
But I will be getting 1000's of rows everyday. How will I give the select stmt in that case ?
With Table1
(
---this select stmt.
)
And could anyone please explain me what does dense_rank () does ?
With Regards,
Anu..;-):hehe:
February 25, 2010 at 5:28 am
Hi.
The statement "With table(...) is a replacement for your table. You don't have to put it on your sql. just the rest. In my posts I use another cte, so you need to mantain the "With".
About dense_rank() basically, it gives you the sequential numbering of the values of the distinct columns in over clause, ordered by it. There is more about it in book online. Or just search it on google.
February 25, 2010 at 5:36 am
Great...worked fine for me.thanks all
With Regards,
Anu..;-):hehe:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply