Transpose of rows to column values
-
Posted - 07/08/2005 : 10:34:45 
Guys, I need to transpose my rows to columns.
This is my scenario with sample data
srcdata subid srcdata2 srcdata3
____________________________________________
1000002 1
2000003 2
3000020 3
1000030 1
2000009 2
3000021 3
I want to populate columns srcdata2 and srcdata3 whenever subid = 1 with the values of srcdata
when subid = 2 and 3 respectively
srcdata subid srcdata2 srcdata3
____________________________________________
1000002 1 2000003 3000020
2000003 2
3000020 3
1000030 1 2000009 3000021
2000009 2
3000021 3
Any suggestions/inputs would be very helpful indeed
Thanks
-
Create two tables one vertical, one horizontal. insert the next min subid in the horizontal table, flag it in the vertical table and then delete it from the vertical table....and so on until all subid's are used up:
/*Create Vertical Table*/
DROP TABLE TEST
Create TABLE TEST
(srcdata varchar (7), subid int)
/*Create Horizontal Table*/
CREATE TABLE SRCDATA
(srcdata varchar (7), subid1 int, subid2 int, subid3 int)
INSERT INTO TEST (srcdata, subid)
Select srcdata, subid
FROM TableNameA
GROUP BY srcdata, subid
Order BY Min(srcdata), Min(subid)
/* CASE 1 */
INSERT INTO SRCDATA (srcdata, subid1)
Select srcdata, Min(subid)as subid1
From TableNameA, TableNameB
Where TableNameA.srcdata = TableNameB.srcdata
Group By srcdata, subid
Order BY Min(srcdata), Min(subid)
Update TEST
SET subid = '1'
FROM SRCDATA, TEST
Where SRCDATA.subid1 = TEST.subid
Delete FROM TEST
Where subid = '1'
/* CASE 2 */
Select srcdata, Min(TEST.subid)as subid2
INTO #TEMP2
From TEST
Group By srcdata
Order By Min(srcdata)
Update SRCDATA
Set SRCDATA.subid2 = #TEMP2.subid2
FROM SRCDATA, #TEMP2
Where SRCDATA.srcdata = #TEMP2.srcdata
DROP TABLE #TEMP2
Update TEST
SET subid = '1'
FROM SRCDATA, TEST
Where SRCDATA.subid2 = TEST.subid
Delete FROM TEST
Where subid = '1'
/* CASE 3 */
Select srcdata, Min(TEST.subid)as subid3
INTO #TEMP3
From TEST
Group By srcdata
Order By Min(srcdata)
Update SRCDATA
Set SRCDATA.subid3 = #TEMP3.subid3
FROM SRCDATA, #TEMP3
Where SRCDATA.srcdata = #TEMP3.srcdata
DROP TABLE #TEMP3
Update TEST
SET subid = '1'
FROM SRCDATA, TEST
Where SRCDATA.subid2 = TEST.subid
Delete FROM TEST
Where subid = '1'
-
In your example
How do you know that 2000003 2 goes with 1000002 1?
Why wouldn't it be 2000009 2?
Or why wouldn't 2000003 2 go with 1000002 1?
srcdata subid srcdata2 srcdata3
____________________________________________
1000002 1 2000003 3000020
2000003 2
3000020 3
1000030 1 2000009 3000021
2000009 2
3000021 3
Is there some piece of information that is missing?
-
Hi,
This is just a frame giving you.. I may be wrong in the way of logic and the way of doing.. You develop further using currect sintax. I am using String building technology for building the SQL statement (Dynamic SQL) and executing it with EXECUTE Command.
----------------------------------------------------------
DECLARE CUR FOR SELECT DISTINCT SUBID, SRCDATA FROM <TableName>
Declare @intCtr, @strSQL, @SQLTemp
SET @intCtr = 0
SET @SQLTemp = ''
OPEN CUR..
Fetch..
if @@Fetch_Status...
begin
@strSQL = 'SELECT SUBID'
Loop Start..
Fetch..
SET @intCtr = @intCtr + 1
@SQLTemp= @SQLTemp + ', CASE SRCDATA = ' + @Value + ' THEN SRCDATA ELSE NULL END AS [SrcData' + convert(varchar,intCtr) + ']'
End Loop..
STRSQL = STRSQL + @SQL + ' FROM <TableName> ' + ' GROUP BY SUBID' + @SQLTemp
End
Close ..
Deallocate..
if @SQLTemp <> ''
Execute (@STRSQL)
--------------------------------------------------------------
Hope it will help you.
Regards,
Ragesh Chavarattil
-
Ray,
The above I am dealing with is a Non relational data which has been designed more than 10 years.
So back then all the information is in 1 column itself .i.e till the next occurence of subid = 1 the it is assumed that subid = 2, 3, 4, 5, ,6 ,7 , 8 belong to the the previous subid = 1.
My quest to convert this data into a relational data, hence I am running into these types of issues.
Thanks
-
Okay, but now you have this data in a sql server table?
or is it still in its native database system?
How do you guarantee the order of the records?
Is there another column that indicates the order. Because in sql server order is not guaranteed, and is usually based on clustered indexes.
Please post the Create Table Statement, and A portion of the records and I'm sure someone would be able to assist you quite quickly.
And also note, is this query for a common query to be run, or are you actually transposing the records to insert into a new table that is properly normalized?
-
< Okay, but now you have this data in a sql server table?
or is it still in its native database system? >
I have ported this data over to sql server table
< How do you guarantee the order of the records? >
Do u suggest to create a table with one column having clustered
index on it and then import.
Because so far I have had no inconsistency with the order
CREATE TABLE [nsourcedata] (
[srcdata] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[subid] [int] NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[srcdata1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[srcdata2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_nsourcedata] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]) ON [PRIMARY]
GO
srcdata subid ID srcdata2 srcdata3
_____________________________________________________
1000301619740214WD 1 1
2000301619740214000 2 2
2000301619740214001 3 3
1000301619740215DT 1 4
2000301619740215000 2 5
2000301619740215020 3 6
The above is the create table statement, since this table has
23 millions I heve ID field with identity primary key to improve the
performance of any update statements that needs to run.
< And also note, is this query for a common query to be run,
or are you actually transposing the records to insert into a
new table that is properly normalized? >
Yes the whole purpose is to insert into a table which is properly
normalized.
As mentioned since I am dealing with 23 millions rows table with
1 column and trying to effectively manipulate to insert into a
normalized table
Appreciate any help and suggestions
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply