Rows to Column

Question

Post reply

Rows to Column

BI_NewBie

SSCrazy

Points: 2261
More actions
December 29, 2014 at 9:20 pm

#397814

Hi All,
Season's Greetings!!
Came across one scenario not able to find out how to do it..
Below is the table data
ColA
1
-1
2
-2
Need output in below way
ColA ColB
1 -1
2 -2
Any response would be highly appreciated
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

Hi,

For the time being you may use this

DECLARE @table TABLE (colA INT)

INSERT INTO @table

SELECT 1

UNION ALL

SELECT -1

UNION ALL

SELECT 2

UNION ALL

SELECT -2

SELECT A.colA,-(B.colA) AS ColB FROM @table AS A

INNER JOIN @table AS B

ON A.colA=B.colA AND ABS(A.Cola)=A.colA

But i m sure this problem may have other very good solutions also. Will post them too once it is done.

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 2

Quite few ways of doing this, here are two quick solutions

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_SAMPLE_A') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_A;

CREATE TABLE dbo.TBL_SAMPLE_A

(

COL_A INT NOT NULL

);

INSERT INTO dbo.TBL_SAMPLE_A (COL_A)

VALUES (1),(-1),(2),(-2);

/* Self-join, will result in two table scans

*/

SELECT

SA.COL_A

,SB.COL_A AS COL_B

FROM dbo.TBL_SAMPLE_A SA

INNER JOIN dbo.TBL_SAMPLE_A SB

ON SA.COL_A = (-1 * SB.COL_A)

WHERE SA.COL_A > 0;

/* Cross-tab, a single scan solution with somewhat

expensive sort operation

*/

SELECT

MAX(CASE WHEN SA.COL_A > 0 THEN COL_A END) AS COL_A

,MAX(CASE WHEN SA.COL_A < 0 THEN COL_A END) AS COL_B

--,ABS(SA.COL_A) AS ABS_COL_A

FROM dbo.TBL_SAMPLE_A SA

GROUP BY ABS(SA.COL_A) ;

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

BI_NewBie (12/29/2014)
Hi All,
Season's Greetings!!
Came across one scenario not able to find out how to do it..
Below is the table data
ColA
1
-1
2
-2
Need output in below way
ColA ColB
1 -1
2 -2
Any response would be highly appreciated
Thanks!

There needs to be some other column in the table to guarantee the order of the rows using an ORDER BY. Do you have such a column in your table? If so, please see the first link under "Helpful Links" in my signature line below to get help on this more quickly.

--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)
Intro to Tally Tables and Functions

BI_NewBie SSCrazy Points: 2261 More actions · Answer 4

Thanks a lot !!!

Really appreciate the kind of help developers receive from this forums.

you guys Rock!!:cool:

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

BI_NewBie (12/29/2014)
Thanks a lot !!!
Really appreciate the kind of help developers receive from this forums.
you guys Rock!!:cool:

You've marked a couple of things as your solutions but be very strongly advised that if the data itself (an your data does look made up) is perfect and in the exact order that you want it to be, you'll need an additional column to keep the order. You simply cannot rely on the order of "natural" order of data because SQL Server doesn't.

--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)
Intro to Tally Tables and Functions