April 11, 2011 at 7:19 am
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.
April 11, 2011 at 8:34 am
I am sure there are many ways to do this ....so here is one idea
insert into Table A (col1)
SELECT B.Col1 + B.Col2 + B.Col3.......
FROM Table B
April 11, 2011 at 8:51 am
This should do the trick:
DECLARE @code_positions nvarchar(max)
DECLARE @column_names nvarchar(max)
DECLARE @sql nvarchar(max)
SET @code_positions =
STUFF
(
(
SELECT ',' + QUOTENAME(CAST(Number AS varchar(2))) AS [text()]
FROM master.dbo.spt_values
WHERE type = 'P'
AND Number BETWEEN 1 AND 50
ORDER BY Number
FOR XML PATH('')
)
, 1, 1, SPACE(0));
SET @column_names =
STUFF
(
(
SELECT ',' + QUOTENAME(CAST(Number AS varchar(2))) + ' AS CODE_POS_' + CAST(Number AS varchar(2)) AS [text()]
FROM master.dbo.spt_values
WHERE type = 'P'
AND Number BETWEEN 1 AND 50
ORDER BY Number
FOR XML PATH('')
)
, 1, 1, SPACE(0));
PRINT @column_names
SET @sql = '
SELECT CRN, ' + @column_names + '
FROM (
SELECT CRN, CODE, CODE_POSITION
FROM PS_TestForOnline
)AS T
PIVOT ( MIN(CODE) FOR CODE_POSITION IN ('+ @code_positions +')) AS P'
PRINT @sql
EXEC(@sql)
Basically, it's just a dynamic pivot, built with dynamic sql.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply