Another Rows to Columns question

Question

Another Rows to Columns question

SQLOzzie

Default port

Points: 1442
More actions
June 16, 2015 at 4:03 pm

#318965

I'm getting data from another system in a stream of <column> <Value> pairs, as below
Column1 100 Main Street
Column2 Appt #1
Column3 Havertown
Column4 PA
Column5 19083
Column5 0121
Column6 USA
Column7 S
Column1 200 Main Street
Column2 Appt #2
Column3 Havertown
Column4 PA
Column5 19083
Column5 0122
Column6 USA
Column7 S
Column1 300 Main Street
Column2 Appt #3
Column3 Havertown
Column4 PA
Column5 19083
Column5 0123
Column6 USA
What technique can I use to flip it to below. I do have a column to pivot on ( Column7 with a value of S )
Column1 Column2 Column3 Column4 Column5 Column6 Column7
100 Main Street Appt #1 Havertown PA 19083 0121 S
200 Main Street Appt #2 Havertown PA 19083 0122 S
300 Main Street Appt #3 Havertown PA 19083 0123 S
This seems like it should be a relatively simple operation - I'm not finding an example of it. An SSIS option would be clutch.:w00t:
Thanks,
Doug

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

How do you identify each group that you're converting to a row? You're missing the E on your EAV design.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLOzzie Default port Points: 1442 More actions · Answer 2

Well, every time I hit Column7 with a value of S, I know I have a row. This was a simplification of the data - I do have an AccountID.

What I am currently doing is adding a third column, AccountID. I RBAR through, setting the AccountID on rows until I hit another 'S'.

Column0 1 1

Column1 100 Main Street 1

Column2 Appt #1 1

Column3 Havertown 1

Column4 PA 1

Column5 19083 1

Column5 0121 1

Column6 USA 1

Column7 S 1

Column0 2 2

Column1 200 Main Street 2

Column2 Appt #2 2

Column3 Havertown 2

Column4 PA 2

Column5 19083 2

Column5 0122 2

Column6 USA 2

Column7 S 2

Column0 3 3

Column1 300 Main Street 3

Column2 Appt #3 3

Column3 Havertown 3

Column4 PA 3

Column5 19083 3

Column5 0123 3

Column6 USA 3

Column7 S 3

Then I roll it out - but that seems like way to much brute force.

Column0 Column1 Column2 Column3 Column4 Column5 Column6 Column7

1 100 Main Street Appt #1 Havertown PA 19083 0121 S

2 200 Main Street Appt #2 Havertown PA 19083 0122 S

3 300 Main Street Appt #3 Havertown PA 19083 0123 S

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Just remember that there's no order inside a table. There's an order inside a flat file, but there's no certainty that SQL Server will load it in the same order.

That said, this is how you can convert your rows into columns.

CREATE TABLE SampleData(

columnname varchar(10),

columnvalue varchar(100),

ID int)

INSERT INTO SampleData VALUES

('Column1', '100 Main Street',1),

('Column2', 'Appt #1 ',1),

('Column3', 'Havertown ',1),

('Column4', 'PA ',1),

('Column5', '19083 ',1),

('Column5', '0121 ',1),

('Column6', 'USA ',1),

('Column7', 'S ',1),

('Column1', '200 Main Street',2),

('Column2', 'Appt #2 ',2),

('Column3', 'Havertown ',2),

('Column4', 'PA ',2),

('Column5', '19083 ',2),

('Column5', '0122 ',2),

('Column6', 'USA ',2),

('Column7', 'S ',2),

('Column1', '300 Main Street',3),

('Column2', 'Appt #3 ',3),

('Column3', 'Havertown ',3),

('Column4', 'PA ',3),

('Column5', '19083 ',3),

('Column5', '0123 ',3),

('Column6', 'USA ',3);

SELECT ID,

MAX( CASE WHEN ColumnName = 'Column1' THEN ColumnValue END) Column1,

MAX( CASE WHEN ColumnName = 'Column2' THEN ColumnValue END) Column2,

MAX( CASE WHEN ColumnName = 'Column3' THEN ColumnValue END) Column3,

MAX( CASE WHEN ColumnName = 'Column4' THEN ColumnValue END) Column4,

MAX( CASE WHEN ColumnName = 'Column5' THEN ColumnValue END) Column5,

MAX( CASE WHEN ColumnName = 'Column6' THEN ColumnValue END) Column6,

MAX( CASE WHEN ColumnName = 'Column7' THEN ColumnValue END) Column7

FROM SampleData

GROUP BY ID;

SELECT ID,

[Column1],

[Column2],

[Column3],

[Column4],

[Column5],

[Column6],

[Column7]

FROM SampleData

PIVOT( MAX( ColumnValue) FOR ColumnName IN ([Column1],

[Column2],

[Column3],

[Column4],

[Column5],

[Column6],

[Column7])) p

GO

DROP TABLE SampleData;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246977 More actions · Answer 4

Douglas Osborne-229812 (6/16/2015)
I'm getting data from another system in a stream of <column> <Value> pairs, as below
Column1 100 Main Street
Column2 Appt #1
Column3 Havertown
Column4 PA
Column5 19083
Column5 0121
Column6 USA
Column7 S
Column1 200 Main Street
Column2 Appt #2
Column3 Havertown
Column4 PA
Column5 19083
Column5 0122
Column6 USA
Column7 S
Column1 300 Main Street
Column2 Appt #3
Column3 Havertown
Column4 PA
Column5 19083
Column5 0123
Column6 USA
What technique can I use to flip it to below. I do have a column to pivot on ( Column7 with a value of S )
Column1 Column2 Column3 Column4 Column5 Column6 Column7
100 Main Street Appt #1 Havertown PA 19083 0121 S
200 Main Street Appt #2 Havertown PA 19083 0122 S
300 Main Street Appt #3 Havertown PA 19083 0123 S
This seems like it should be a relatively simple operation - I'm not finding an example of it. An SSIS option would be clutch.:w00t:
Thanks,
Doug

While I have no idea, beyond car gear systems, what a 'clutch' is, I can confirm that an asynchronous script component in SSIS would handle this, if you are prepared to write a little code.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQLOzzie Default port Points: 1442 More actions · Answer 5

Phil,

Are you referring to a task like the one described here?

http://stackoverflow.com/questions/19521920/ssis-data-transformation-rows-to-COLUMNS

Thanks for the assist,

Doug

SQLOzzie Default port Points: 1442 More actions · Answer 6

Luis,

That was exactly what I needed. Once I completed the second SQL - I was good to go. I guess when I had tried that technique myself I had implemented it wrong.

What does the 3rd SQL specifically do? The 2nd SQL was the exampleI needed.

Thanks,

Doug

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

I posted 2 options. The first is called cross tabs and the second one uses the PIVOT operator.

You can read more about this approaches in here: http://www.sqlservercentral.com/articles/T-SQL/63681/

I also posted DDL and sample data to help anyone to copy and paste to test the code easily.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246977 More actions · Answer 8

Douglas Osborne-229812 (6/17/2015)
Phil,
Are you referring to a task like the one described here?
http://stackoverflow.com/questions/19521920/ssis-data-transformation-rows-to-COLUMNS
Thanks for the assist,
Doug

Not quite – that one is synchronous. I've looked for a decent example and cannot find one. If you are still interested, I will post something with a bit more info, but it sounds like you have a solution already.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

Douglas Osborne-229812 (6/17/2015)
Luis,
That was exactly what I needed. Once I completed the second SQL - I was good to go. I guess when I had tried that technique myself I had implemented it wrong.
What does the 3rd SQL specifically do? The 2nd SQL was the exampleI needed.
Thanks,
Doug

It's only an example because, even a Luis stated, you cannot rely on the insert order into a table being preserved or even established. This code WILL blow up on you someday in the future and the worst part may be that it won't produce a visible error when it does. You must find the "E" that Luis was speaking of and incorporate that.

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

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

Luis Cazares (6/16/2015)
Just remember that there's no order inside a table. There's an order inside a flat file, but there's no certainty that SQL Server will load it in the same order.

I absolutely love your willingness to help people but, in this case and to be totally honest, I wouldn't have published a solution to a problem that we both know will eventually break because of what you so accurately and properly stated. In this case, I believe the OP and the company they work for needs to be protected from their own beliefs.

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