SQL Server Performance Forum – Threads Archive

Pivoting Columns

Hi Everybody! I’m trying to pivot the following table:
[Id],[Col 1],[Col 2],[Col3]…Upto [Col15].
The result I want is:
[Id],[Col1]
[Id],[Col2]
[Id],[Col3]
etc… The table has about 200 000 + records. What is the fastest way of dong this? I have tried using a UNION and also a CASE statement but both takes 25 seconds. (Is this what I’m suppose to get?) I have a clustered key on the table (Id) Can anyone help? Thanks

---

If you are using UNION rather than UNION ALL you will be selecting distinct rows and as such the server probably needs to do a lot more processing, if you use union all then you may get repeats. For the following data in table1 ID|Col1|Col2|Col3|Col4
1|A|B|C|D
2|A|A|E|F SELECT ID, Col1 AS output
from table1
UNION
SELECT ID, Col2 AS output
from table1
UNION
SELECT ID, Col3 AS output
from table1
UNION
SELECT ID, Col4 AS output
from table1 This will yield
ID|output
1|A
1|B
1|C
1|D
2|A
2|E
2|F Note that the second instance of "A" in row 2 is not returned However SELECT ID, Col1 AS output
from table1
UNION ALL
SELECT ID, Col2 AS output
from table1
UNION ALL
SELECT ID, Col3 AS output
from table1
UNION ALL
SELECT ID, Col4 AS output
from table1 This will yield
ID|output
1|A
1|B
1|C
1|D
2|A
2|A
2|E
2|F It depends whether or not your are concerned about duplicate rows in your output. If the data in each of the columns you want to report on can never be the same as the data any of the other columns you may find UNION ALL a better solution. However no guarantees that it will speed it up as you may have to write more data to disk if you include duplicates which you have already excluded Regards, Bob.

---

Thanks for the reply. It doesn’t matter if the result contains duplicates. Will a UNION ALL be faster than using a Case statement? Do you think dynamic sql is maybe another option? Hacc

---

]]>