ansaurus

Question

Answer 1

+3 A:

Since you are using SQL Server 2005 you can use UNPIVOT. This is modified from the example on that page:

SELECT ColA, ColB
FROM Table1
UNPIVOT (ColB FOR ColA IN ([SWD Lead],
                           [QA Lead],
                           [PD Lead],
                           [Business Manager])) AS unpvt;

Result:

ColA              ColB
SWD Lead          AAAA
QA Lead           BBBB
PD Lead           CCCC
Business Manager  DDDD

On older server versions you could do something like this to achieve the same effect:

SELECT 'Col1', Col1 FROM Table1
UNION ALL
SELECT 'Col2', Col2 FROM Table1
UNION ALL
SELECT 'Col3', Col3 FROM Table1
UNION ALL
SELECT 'Col4', Col4 FROM Table1

Mark Byers 2010-02-04 09:17:46

SELECT ColA,ColBFROM * (SELECT * FROM dbo.table ) p UNPIVOT (ColA FOR ColB IN ('QA Lead', 'SW Lead')) As unpvtI got a error "Msg 102, Level 15, State 1, Line 2Incorrect syntax near '*'.Msg 102, Level 15, State 1, Line 4Incorrect syntax near 'p'."

Yongwei Xing 2010-02-04 09:26:21

+1, useful answer. Slight correction - when I pasted this into management studio, it required 'as my_pivot_table_name;' at the end

James Wiseman 2010-02-04 09:27:56

@Yonwei Xing: You have an incorrect * after your first FROM. Instead of posting your question with fake column names, just include your real column names in the question and I can create the query to exactly work in your situation so that you don't have to modify it at all. This will reduce the risk of you making a mistake while changing the column names, as the query will work as-is.

Mark Byers 2010-02-04 09:28:03

@Mark Byers: Thnaks very much. There are four colum names QA Lead, SWD Lead, PD Lead, Business Manager.

Yongwei Xing 2010-02-04 14:22:56

Your column names are slightly tricky because they include spaces. To get around this you can surround the names by square brackets. See my updated answer.

Mark Byers 2010-02-04 14:33:53

Thank you very much, I will try it later.

Yongwei Xing 2010-02-04 14:45:00

ansaurus

tags:

views:

answers:

Make a one line data to a table in SQL

related questions