Columns to rows and rows to columns

Question

Columns to rows and rows to columns

p.shabbir

SSCrazy

Points: 2404
More actions
March 24, 2017 at 1:17 pm

#319911

Hi,
I have table like below.
START_DATE    END_DATE    COUNTRY        VALUES
1/1/2017        2/1/2017        USA                      20
1/1/2017        2/1/2017        INDIA          50
1/1/2017        2/1/2017        AUSTRALIA   40
I need O/P as below
START_DATE    1/1/2017        1/1/2017        1/1/2017
END_DATE       2/1/2017        2/1/2017        2/1/2017
COUNTRY        USA       INDIA       AUSTRALIA
VALUES       20             50                   40
help me please....
Thnx in advnc.

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

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

drew.allen SSC Guru Points: 76776 More actions · Answer 1

This should be done in the presentation layer, not the database layer. SQL Server is highly typed, which means that all of the values in a column have to have the same data type. This transposition would require you to convert your dates and integers to character in order to have the same data type, thereby losing all of the benefits of working with those values in their natural data types.

Furthermore, you give no indication of how many columns you would need and what to do when the number of rows exceeds the number of columns.

SQL Server is not Excel. If you want to do these kinds of transformations, export to Excel and transform it in Excel.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

p.shabbir SSCrazy Points: 2404 More actions · Answer 2

drew.allen - Friday, March 24, 2017 1:45 PM
This should be done in the presentation layer, not the database layer. SQL Server is highly typed, which means that all of the values in a column have to have the same data type. This transposition would require you to convert your dates and integers to character in order to have the same data type, thereby losing all of the benefits of working with those values in their natural data types.
Furthermore, you give no indication of how many columns you would need and what to do when the number of rows exceeds the number of columns.
SQL Server is not Excel. If you want to do these kinds of transformations, export to Excel and transform it in Excel.
Drew

Thanks for the response, Allen.
I have 18 columns and 3 rows
Columns are fixed (18),rows may vary.

Thnx.

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

p.shabbir - Friday, March 24, 2017 1:17 PM
Hi,
I have table like below.
START_DATE    END_DATE    COUNTRY        VALUES
1/1/2017        2/1/2017        USA                      20
1/1/2017        2/1/2017        INDIA          50
1/1/2017        2/1/2017        AUSTRALIA   40
I need O/P as below
START_DATE    1/1/2017        1/1/2017        1/1/2017
END_DATE       2/1/2017        2/1/2017        2/1/2017
COUNTRY        USA       INDIA       AUSTRALIA
VALUES       20             50                   40
help me please....
Thnx in advnc.

What's the purpose? If the ultimate target of the requested output is a spreadsheet or report, then I absolutely agree with Drew... do the transformation in that spreadsheet or report. Same goes if it's just a "one off".

If a spreadsheet or report isn't the ultimate target for this data, then what is the business reason for wanting this transformation? And, no... "Someone asked for it" isn't a "business reason". 😉 The reason we ask such questions is because it helps us understand your problem well enough to figure out if there might be a better way for you and the company you work for.

Same goes if it's just a "one off".

--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

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

Apparently, the OP has left the building.

--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