Data Warehouse - Rows to columns Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data Warehouse - Rows to columns Query

Author

Topic

sqlnovice1
Starting Member

34 Posts

Posted - 2011-04-11 : 09:19:45

Hi All,

Back for some more advice and learning.

Having transfered our data from our very old system into a new Data Warehouse, the format of data has changed.

We used to have strings of data held in 1 field

example 'A1ýA2ýA3ýA4' these were read from right to left.

We use to extract these data fields intop excel and use the text-to-column feature to split accross cols by the 'ý' symbol.

The new Data Warehouse has re0assigned these multi-value fields into rows and assigned them a position which relates to ther old position in the string.

EXAMPLE DATA

drop table PS_TestForOnline

CREATE TABLE PS_TestForOnline
(
rowkey int,
crn int,

CODE NVARCHAR (5) ,
CODE_POSITION INT,

)

INSERT INTO PS_TestForOnline
VALUES('1','11111','A1','1' );
INSERT INTO PS_TestForOnline
VALUES('2','11111','A2','2');
INSERT INTO PS_TestForOnline
VALUES('3','11111','A3','3');
INSERT INTO PS_TestForOnline
VALUES('4','11111','A4','4');
INSERT INTO PS_TestForOnline
VALUES('5','22222','B1','1' );
INSERT INTO PS_TestForOnline
VALUES('6','22222','B2','2' );
INSERT INTO PS_TestForOnline
VALUES('7','22222','B3','3');
INSERT INTO PS_TestForOnline
VALUES('8','22222','B4', '4');

select * from PS_TestForOnline

I need to beable to query this data so that the results are shown as follows ie: a single row for each unique CRN. note i may have upto 50 positions.

EXPECTED RESULTS

drop table PS_TestForOnline_Answer

CREATE TABLE PS_TestForOnline_Answer
(
crn int,
CODE_POS_1 NVARCHAR (5),
CODE_POS_2 NVARCHAR (5),
CODE_POS_3 NVARCHAR (5),
CODE_POS_4 NVARCHAR (5),
);

INSERT INTO PS_TestForOnline_Answer
VALUES('11111','A1', 'A2','A3','A4' );
INSERT INTO PS_TestForOnline_Answer
VALUES('22222','B1', 'B2','B3','B4' );

select * from PS_TestForOnline_Answer

Many Thanks in advance for any advice given.

Paul

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-11 : 11:18:16

Since you have an unknown number of columns in the pivoted result, you would need dynamic pivoting. See Madhivanan's post here for an example of how to do it. http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

The process is simple - you install the stored procedure that Madhivanan has in his blog. Then just use it, for example like this:

EXEC dynamic_Pivot 
'SELECT crn,code FROM PS_TestForOnline',
'Code_Position', 'max(Code)';

If it works for you, thank Madhivanan. If there are any problems/issues you run into, post them back here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources