Converting rows into columns

Question

Converting rows into columns

ldelacru1995

SSC Enthusiast

Points: 107
More actions
January 24, 2009 at 6:08 pm

#94762

I have a db2 table that contains a data structure as shown below:
Id_solution Parameter_nm Parameter_value
11111111 "Data1":"Sample1" 9
11111111 "Table1" 7
11111111 "Effective Date":"Format" 12/01/2009
12111111 "Data1":"Sample1" 10
12111111 "Table1" 8
12111111 "Effective Date":"Format" 12/31/2009
The result I want if I query using id_solution is as follows:
Id_solution Source1 Source2 Date1
11111111 9 7 12/01/2009
12111111 10 8 12/31/2009
Using source1 as an alias for "Data1":"Sample1" , source2 as an alias for "Table1" and Date1 as an alias for "Effective Date":"Format".
There are about 1400 rows that I need to convert into columns.
Any help will be very much appreciated.
Thanks in advance.

Viewing 8 posts - 1 through 8 (of 8 total)

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

Dominique Plante Grasshopper Points: 13 More actions · Answer 1

Hi,

If this is a one time type of thing, it's a pretty straightforward to do in excel using PivotTables. Like you, I will be curious to see the "T-SQL" way of doing this.

By the way, there is a name for the way the original data you are trying to transform is formatted, and it's called "Entity Attribute Value" Model or EAV.

1. paste data into excel

2. split all data into rows and columns distinctly (use text to table to help)

3. I would change the values in parameter_nm column to be what you want in output column

Your data should be separate cells like:

idnamevalue

11111111Source19

11111111Source27

11111111Date12/1/2009

12111111Source110

12111111Source28

12111111Date12/31/2009

Leave column headers to help pivot table wizard

4. create pivot table (data -> pivottable and pivotchart wizard) - select your data as input (including column headers), specify whatever output makes sense

5. drag id column from pivottable field list to 'drop row fields here', drag name column from pivottable field list to 'drop column fields here' and finally drag value column from pivottable field list to 'drop data items here'

You are almost done

6. double click in top left field of pivot table - the one that reads count of value - select summarize by 'min'

If your data looks like 40148, change column format to data.

Your data should now look like:

id Date Source1Source2

1111111112/1/20099 7

1211111112/31/200910 8

By the way,

http://en.wikipedia.org/wiki/Entity-Attribute-Value_model hinted that it's sometimes easier to use access and http://articles.techrepublic.com.com/5100-10878_11-1042409.html hinted that access was providing similar functionality to Excel and also has a pivot table tutorial of its own.

Hope this help!

Thanks!

Dominique

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 2

Plz take a look at PIVOT in BOL.

CREATE TABLE #Test

(Id_solution INT, Parameter_nm VARCHAR(100), Parameter_value VARCHAR(50))

INSERT INTO #Test

(Id_solution, Parameter_nm, Parameter_value)

select 11111111, '"Data1":"Sample1"', '9' union all

select 11111111, '"Table1"', '7' union all

select 11111111, '"Effective Date":"Format"', '12/01/2009' union all

select 12111111, '"Data1":"Sample1"', '10' union all

select 12111111, '"Table1"', '8' union all

select 12111111, '"Effective Date":"Format"', '12/31/2009'

go

select Id_solution, ["Data1":"Sample1"] as Source1, ["Table1"] as Source2, ["Effective Date":"Format"] as Date1 from

(SELECT Id_solution

, Parameter_nm

, Parameter_value

FROM #Test) p

PIVOT

(

MAX(Parameter_value)

FOR Parameter_nm IN

( ["Data1":"Sample1"], ["Table1"], ["Effective Date":"Format"] )

) AS pvt

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Old-fashioned SQL works well too:

SET DATEFORMAT MDY

DROP TABLE #Temp

CREATE TABLE #Temp (Id_solution INT, Parameter_nm VARCHAR(50), Parameter_value VARCHAR(50))

INSERT INTO #Temp (Id_solution, Parameter_nm, Parameter_value)

SELECT 11111111, '"Data1":"Sample1"','9' UNION ALL

SELECT 11111111, '"Table1"','7' UNION ALL

SELECT 11111111, '"Effective Date":"Format"','12/01/2009' UNION ALL

SELECT 12111111, '"Data1":"Sample1"','10' UNION ALL

SELECT 12111111, '"Table1"','8' UNION ALL

SELECT 12111111, '"Effective Date":"Format"','12/31/2009'

SELECT Id_solution,

CAST(MAX(CASE WHEN Parameter_nm = '"Data1":"Sample1"' THEN Parameter_value END) AS INT) AS [Source1],

CAST(MAX(CASE WHEN Parameter_nm = '"Table1"' THEN Parameter_value END) AS INT) AS [Source2],

CAST(MAX(CASE WHEN Parameter_nm = '"Effective Date":"Format"' THEN Parameter_value END) AS DATETIME) AS [Date1]

FROM #Temp

GROUP BY Id_solution

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

And, if you take a look at the following article, you'll find the "old fashioned way" actually has a bit of a performance edge over the PIVOT method...

[font="Arial Black"]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/font][/url]

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

ldelacru1995 SSC Enthusiast Points: 107 More actions · Answer 5

Hi,

Thanks for the reply. But this is not what I am looking for.

I am using a DBMS software and I will need to use SQL to build the query. I will need to create a report out of the result

set.

Do appreciate your response.

ldelacru1995 SSC Enthusiast Points: 107 More actions · Answer 6

Hi,

This is what I am looking for.

Thank you for your response.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

The other thing is that SQL is not SQL between vendors... you're looking for a DB2 solution which could be quite different than the SQL Server solutions you'll get. And, this is an SQL Server forum...

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