transform data - SQL Server Forums

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

transform data

Author

Topic

learnsql123
Starting Member

23 Posts

Posted - 2012-10-26 : 23:00:12

I have some data generated by an application which needs to be transformed for another application to be able to use it. Sample of my data is shown in data set, and the transformed data is shown in result set.
Data set: comma seperated to show as I cannot attach snapshot
TID,PN,PV
1,akey,123
1,akey,234
1,akey,567
1,pkey,345
1,lkey,321

2,mid,123
2,mid,223
2,lid,111

3,pkey,123
3,lkey,234

Transformed data:
TID,PN,PV,Group
1,akey,123,1
1,akey,234,2
1,akey,567,3
1,pkey,345,1
1,pkey,345,2
1,pkey,345,3
1,lkey,321,1
1,lkey,321,2
1,lkey,321,3

2,mid,123,1
2,mid,223,2
2,lid,111,1
2,lid,111,2

3,pkey,123,1
3,lkey,234,1

If you notice for TID 1, there are three PV values for PN akey and one PV value each for PN pkey and lkey. I want to insert pkey and lkey data as many times as akey, and remove the original rows, and also assign group numbers as shown in result set. So In the result set I assigned group num 1,2,3 to akey rows and copied pkey and lkey three times each with group nums as in akey.

Similarly, For TID = 2, I have 2 mid rows and one lid row. The resultset would have the lid row twice with group = 1 and 2.

for TID = 3 I have one value for pkey and one value for lkey. The result set would have the same data with group num 1 in both rows.

This is just an example. There can be any combination, where one of the PN would have multiple PV values for same TID, and other PN rows for the same TID will have one value. The single row PN data will have to be inserted as many times as the multiple row PN is and then create sequential groups. I hope I am able to explain it.

What would be the best sql way to transform this data.

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-27 : 00:20:51

[code]
declare @test table
(
TID int,
PN varchar(10),
PV int
)
insert @test
values
(1,'akey',123),
(1,'akey',234),
(1,'akey',567),
(1,'pkey',345),
(1,'lkey',321),

(2,'mid',123),
(2,'mid',223),
(2,'lid',111),

(3,'pkey',123),
(3,'lkey',234)

;With CTE
AS
(
SELECT *,
COUNT(1) OVER (PARTITION BY TID,PN) AS Cnt
FROM @test
),CTE1
AS
(
SELECT c.*,MaxCnt
FROM CTE c
INNER JOIN (SELECT TID,MAX(Cnt) AS MaxCnt
FROM CTE
GROUP BY TID)c1
ON c1.TID = c.TID
)

SELECT TID,
PN,
PV
FROM CTE1
CROSS JOIN master..spt_values v
WHERE v.number BETWEEN 1 AND MaxCnt
AND v.type='p'
AND Cnt <> MaxCnt

UNION ALL
SELECT TID,
PN,
PV
FROM CTE1
WHERE Cnt=MaxCnt
ORDER BY TID

output
---------------------------
TID PN PV
---------------------------
1 akey 123
1 akey 234
1 akey 567
1 lkey 321
1 lkey 321
1 lkey 321
1 pkey 345
1 pkey 345
1 pkey 345
2 lid 111
2 lid 111
2 mid 123
2 mid 223
3 lkey 234
3 pkey 123

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-27 : 00:33:07

Simplified version


declare @test table
(
TID int,
PN varchar(10),
PV int
)
insert @test
values
(1,'akey',123),
(1,'akey',234),
(1,'akey',567),
(1,'pkey',345),
(1,'lkey',321),

(2,'mid',123),
(2,'mid',223),
(2,'lid',111),

(3,'pkey',123),
(3,'lkey',234)

;With CTE
AS
(
SELECT *,
COUNT(1) OVER (PARTITION BY TID,PN) AS Cnt
FROM @test
),CTE1
AS
(
SELECT c.*,1 AS Cnt1,MaxCnt
FROM CTE c
INNER JOIN (SELECT TID,MAX(Cnt) AS MaxCnt
            FROM CTE
			GROUP BY TID)c1
			ON c1.TID = c.TID
			UNION ALL
			SELECT TID,PN,PV,Cnt,Cnt1+1,MaxCnt
			FROM CTE1
			WHERE Cnt1 + 1 < = Maxcnt
			AND Cnt =1
)



SELECT TID,PN,PV
FROM CTE1
ORDER BY TID,PN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learnsql123
Starting Member

23 Posts

Posted - 2012-10-27 : 09:33:43

Thanks visakh16
It inserted the right rows, but did not put the number in group column. Please help a little more.

Thanks for your help.

learnsql123
Starting Member

23 Posts

Posted - 2012-10-27 : 12:12:16

I figured it out to get desired result taking hints from the sql that you provided, but my sql is not so crisp as yours. Any suggestions to make it crisp to avoid all those table definitions in the beginning is welcome.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-27 : 21:17:42

quote:
Originally posted by learnsql123

I figured it out to get desired result taking hints from the sql that you provided, but my sql is not so crisp as yours. Any suggestions to make it crisp to avoid all those table definitions in the beginning is welcome.

Thanks.

thats just for illustration you can use suggestion alone replacing tables with your actual table names

ie use only this


;With CTE
AS
(
SELECT *,
COUNT(1) OVER (PARTITION BY TID,PN) AS Cnt
FROM your table here
),CTE1
AS
(
SELECT c.*,1 AS Cnt1,MaxCnt
FROM CTE c
INNER JOIN (SELECT TID,MAX(Cnt) AS MaxCnt
            FROM CTE
			GROUP BY TID)c1
			ON c1.TID = c.TID
			UNION ALL
			SELECT TID,PN,PV,Cnt,Cnt1+1,MaxCnt
			FROM CTE1
			WHERE Cnt1 + 1 < = Maxcnt
			AND Cnt =1
)



SELECT TID,PN,PV
FROM CTE1
ORDER BY TID,PN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learnsql123
Starting Member

23 Posts

Posted - 2012-10-28 : 12:20:00

Here is the output that i want, by my code has table declarations, I think some of those may be avoided using ';with' clause. I tried, but could work without those table declarations. Can you please help me optimize this code, so that It works without those table declarations.

Thanks.


declare @AllCnt table
(
TID int,
PN varchar(10),
PV int, 
Cnt int
)
declare @Singles table 
(
TID int,
PN varchar(10),
PV int, 
Cnt int
)

declare @Multi table 
(
TID int,
PN varchar(10),
PV int, 
Cnt int
)

declare @singleGrp table 
(
TID int,
PN varchar(10),
PV int, 
grp int
)

declare @MultiGrp table 
(
TID int,
PN varchar(10),
PV int, 
grp int
)


declare @test table
(
TID int,
PN varchar(10),
PV int
)
insert @test
values
(1,'akey',123),
(1,'akey',234),
(1,'akey',567),
(1,'pkey',345),
(1,'lkey',321),

(2,'mid',123),
(2,'mid',223),
(2,'lid',111),

(3,'pkey',123),
(3,'lkey',234)

;With CTE
AS
(
SELECT *,
COUNT(1) OVER (PARTITION BY TID,PN) AS Cnt
FROM @test
)
Insert into @AllCnt
Select * from CTE

--Select * from @AllCnt

Insert into @Singles
SELECT TID,PN,PV, cnt
FROM @AllCnt
where Cnt = 1

Insert into @Multi
SELECT TID,PN,PV, cnt
FROM @AllCnt
where Cnt >1

--Select * from @Singles
--Select * from @Multi

--=============================================================
Insert into @MultiGrp
SELECT TID, PN, PV,
ROW_NUMBER() OVER (PARTITION BY TID,PN order by TID,PN, PV) AS grp
FROM @Multi


Insert into @SingleGrp
SELECT S.TID, S.PN,S.PV, M.grp
From @MultiGrp M
Join @Singles S on S.TID = M.TID

--Select * from @MultiGrp
--Select * from  @SingleGrp

Select * from 
(Select * from @MultiGrp
	Union All
Select * from @SingleGrp
)as tmp
order by TID, grp, PN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 09:39:22

whats the need of these intermediate tables?
singlegrp,multigrp etc

whats the issue with my suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learnsql123
Starting Member

23 Posts

Posted - 2012-10-29 : 12:09:13

visakh16, your script is not giving me the last column (grp). I need that.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 12:20:01

for that you dont need all these. just a small addition to my earier suggestion


;With CTE
AS
(
SELECT *,
COUNT(1) OVER (PARTITION BY TID,PN) AS Cnt
FROM your table here
),CTE1
AS
(
SELECT c.*,1 AS Cnt1,MaxCnt
FROM CTE c
INNER JOIN (SELECT TID,MAX(Cnt) AS MaxCnt
            FROM CTE
			GROUP BY TID)c1
			ON c1.TID = c.TID
			UNION ALL
			SELECT TID,PN,PV,Cnt,Cnt1+1,MaxCnt
			FROM CTE1
			WHERE Cnt1 + 1 < = Maxcnt
			AND Cnt =1
)



SELECT TID,PN,PV,ROW_NUMBER() OVER (PARTITION BY TID,PN ORDER BY PV) AS Seq
FROM CTE1
ORDER BY TID,PN,Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learnsql123
Starting Member

23 Posts

Posted - 2012-10-29 : 19:40:00

Thank you. That works perfect.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 19:44:59

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources