Convert columns as rows

Question

Convert columns as rows

lefrancisco1

Say Hey Kid

Points: 704
More actions
January 28, 2011 at 3:49 am

#232900

Folks,
I have three subqueries i made them as single and returned three columns as a|b|c
now i need to changes this into single rows
as
a
b
c
need urgent help

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

do you have any examples of what you are doing? It's easier if you provide code, sample data and expected results instead of some ambiguous urgent request

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 2

Hi

Refer this post..It will help

http://www.sqlservercentral.com/Forums/Topic791603-5-1.aspx

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

sound like UNPIVOT to me, although you have a very sparse description of what you actually need and no sample data/code/expected outcome.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

lefrancisco1 (1/28/2011)
Folks,
I have three subqueries i made them as single and returned three columns as a|b|c
now i need to changes this into single rows
as
a
b
c
need urgent help

Does each subquery return one of each of the columns? If so, just don't do that. Have them all return to the same column using UNION ALL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

lefrancisco1 (1/28/2011)
Folks,
I have three subqueries i made them as single and returned three columns as a|b|c
now i need to changes this into single rows
as
a
b
c
need urgent help

Simple pivot example: -

DECLARE @table AS TABLE(Id INT, Product CHAR(5), TT INT, Week CHAR(2))

INSERT INTO @table

SELECT 1, 'Test1', 75, '1'

UNION ALL SELECT 2, 'Test2', 20, '1'

UNION ALL SELECT 3, 'Test1', 60, '2'

UNION ALL SELECT 4, 'Test2', 40, '2'

UNION ALL SELECT 5, 'Test1', 35, '3'

UNION ALL SELECT 6, 'Test2', 65, '3'

SELECT pivoteddata.*

FROM (SELECT product, tt, week

FROM @table) AS rawdata

PIVOT (MAX(tt) FOR week IN ([1], [2], [3])) AS pivoteddata

Also, read Jeff's wonderful articles -Part 1[/url], Part 2[/url]

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

skcadavre (2/1/2011)
lefrancisco1 (1/28/2011)
Folks,
I have three subqueries i made them as single and returned three columns as a|b|c
now i need to changes this into single rows
as
a
b
c
need urgent help
Simple pivot example: -
DECLARE @table AS TABLE(Id INT, Product CHAR(5), TT INT, Week CHAR(2))
INSERT INTO @table
SELECT 1, 'Test1', 75, '1'
UNION ALL SELECT 2, 'Test2', 20, '1'
UNION ALL SELECT 3, 'Test1', 60, '2'
UNION ALL SELECT 4, 'Test2', 40, '2'
UNION ALL SELECT 5, 'Test1', 35, '3'
UNION ALL SELECT 6, 'Test2', 65, '3'
SELECT pivoteddata.*
FROM (SELECT product, tt, week
FROM @table) AS rawdata
PIVOT (MAX(tt) FOR week IN ([1], [2], [3])) AS pivoteddata
Also, read Jeff's wonderful articles -Part 1[/url], Part 2[/url]

I really appreciate the glowing review but the op wants to UNpivot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 7

What about:

SELECT 'a'

UNION

SELECT 'b'

UNION

SELECT 'c'

Regards,

Iulian

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

Jeff Moden (2/1/2011)
I really appreciate the glowing review but the op wants to UNpivot.

And that's what comes from skimming the first post and not reading the thread. . .

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

What am I - chopped liver? I think I did mention UNpivot, right?? :hehe:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Cadavre SSC-Forever Points: 41690 More actions · Answer 10

TheSQLGuru (2/1/2011)
What am I - chopped liver? I think I did mention UNpivot, right?? :hehe:

Yep yep, my bad. Skimmed the first post. Only came on here because I was thinking about how to re-write an awful stored procedure that I've inherited. I'm going to go and sit in the naughty corner for awhile, k? :blush:

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/