quote:

---

Originally posted by tkizer

I'm sure that there is a more efficient way to do it, but here's what I came up with:

SELECT DISTINCT DataCol1 AS NewCol FROM Table1
UNION
SELECT DISTINCT DataCol2 FROM Table2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

SELECT DISTINCT Data
FROM
(SELECT DatCol1,DataCol2 FROM Table)m
UNPIVOT (Data FOR Values IN ([DataCol1],[DataCol2]))u

quote:

---

Originally posted by onlyforme

Hi,
This will meet ur pblm ,but the only condition is both fields must be of same data type.
select distinct field1 from datacol1
union
select distinct field2from datacol2

---

quote:

---

Originally posted by visakh16

if sql 2005 you can do this also

SELECT DISTINCT Data
FROM
(SELECT DatCol1,DataCol2 FROM Table)m
UNPIVOT (Data FOR Values IN ([DataCol1],[DataCol2]))u

---

quote:

---

Originally posted by calvinkwoo3000

quote:

---

Originally posted by visakh16

if sql 2005 you can do this also

SELECT DISTINCT Data
FROM
(SELECT DatCol1,DataCol2 FROM Table)m
UNPIVOT (Data FOR [Values] IN ([DataCol1],[DataCol2]))u

---

Visakh16, thank your reply.
But when i try to execute your query it show me error.
Message is "Incorrect syntax near keyword VALUES"

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by calvinkwoo3000

quote:

---

Originally posted by visakh16

if sql 2005 you can do this also

SELECT DISTINCT Data
FROM
(SELECT DatCol1,DataCol2 FROM Table)m
UNPIVOT (Data FOR [Values] IN ([DataCol1],[DataCol2]))u

---

Visakh16, thank your reply.
But when i try to execute your query it show me error.
Message is "Incorrect syntax near keyword VALUES"

---

put square braces around values

---

Your Other query...
INNER JOIN 
(
SELECT DISTINCT Data
FROM
(SELECT DatCol1,DataCol2 FROM Table)m
UNPIVOT (Data FOR [Values] IN ([DataCol1],[DataCol2]))u
)t
ON t.Data=yourotherquerylinkingfield

quote:

---

Originally posted by visakh16

something like...

Your Other query...
INNER JOIN 
(
SELECT DISTINCT Data
FROM
(SELECT DatCol1,DataCol2 FROM Table)m
UNPIVOT (Data FOR [Values] IN ([DataCol1],[DataCol2]))u
)t
ON t.Data=yourotherquerylinkingfield

---

quote:

---

Originally posted by tkizer

But which is more efficient?

---

/*
CREATE TABLE tstPeso (Col1 INT, Col2 INT)

insert tstPeso (col1, col2)
SELECT top 1000000 abs(checksum(newid())) % 3000, abs(checksum(newid())) % 3000
from master..spt_values as v1
cross join master..spt_values as v2
where v1.type = 'p' and v2.type = 'p'
*/
-- Tara 31% of batch, CPU 672, DURATION 743, READS 4230
SELECT DISTINCT Col1 AS NewCol FROM tstPeso
UNION
SELECT DISTINCT Col2 FROM tstPeso
option (maxdop 1)

-- Tara 2 31% of batch, CPU 719, DURATION 853, READS 4230
SELECT Col1 AS NewCol FROM tstPeso
UNION
SELECT Col2 FROM tstPeso
option (maxdop 1)

-- visakh 37% of batch, CPU 641, DURATION 759, READS 2115
SELECT DISTINCT Data
FROM
(SELECT Col1,Col2 FROM tstpeso)m
UNPIVOT (Data FOR [Values] IN ([Col1],[Col2]))u
option (maxdop 1)