Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select columns in rows

Author  Topic 

mthissen
Starting Member

2 Posts

Posted - 2014-04-14 : 11:25:22
Hello, I am new to this forum and I have a question. Can somebody give
a hint or solution? Thanks in advance.

I have a simple query like this:

select test1, test2
from testtable

Output =

test1 test2
----- -----
1 34
1 36
2 48
3 54
3 67
3 69


I would like to get my output like this:

test1 test2 test2 test2
----- ----- ----- -----
1 43 36
2 48
3 54 67 69

Can I fix this in a sql query?

Greetz, Mark

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-14 : 11:53:40
Use the PIVOT operator. There is documentation here: http://technet.microsoft.com/en-us/library/ms177410(v=sql.100).aspx

The following is an example. When you ask a question, if you post the statements that create the table and populate the data (the first two lines in the code below) that makes it easier for someone to write the code against it.
CREATE TABLE #test(test1 INT, test2 INT);
INSERT INTO #test VALUES (1 ,34),(1 ,36),(2, 48),(3, 54),(3, 67),(3 ,69)

;WITH cte AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY test1 ORDER BY test2 ) AS RN
FROM #test
)
SELECT * FROM
cte
PIVOT (MAX(test2) FOR RN IN ([1],[2],[3],[4]))P

DROP TABLE #test;
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-04-15 : 07:00:54
For dynamic number of RN, refer this post http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mthissen
Starting Member

2 Posts

Posted - 2014-04-18 : 09:51:20
Thank you, I almost got it!

I have tried it today and couldn't get it right. The query delivers the correct answer but my "test2" is a varchar so I get the error message "Operand data type uniqueidentifier is invalid for max operator". I have looked in the forum and tried several things but I did not get a satisfied result. I think I will try it with a loop and temp table or do you have a suggestion to get it right in this query?
Go to Top of Page
   

- Advertisement -