multiple rows 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

multiple rows data

Author

Topic

alhakimi
Starting Member

23 Posts

Posted - 2013-05-22 : 17:12:31

Hi friends

i have another query i need ur help plz,
I have the following view that has multiple row with almost same data except each row has one or two columns differnet information
currently the view like this :
C1 c2 C3 C4 C5
row1 data data name1 job
row1 data data name2 job
row2 data data name3 job
row2 data data name4 job

as u can see above column one has two rows with the same data but differnece in c4
i need it like this :
C1 c2 C3 C4 C5 c6 c7
row1 data data name1 job name2 job
row2 data data name3 job name4 job

please help
thank you

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-22 : 21:40:36

Here is a way to get what you want:
[CODE]

DECLARE @TEMP TABLE(C1 VARCHAR(10), C2 VARCHAR(10), C3 VARCHAR(10), C4 VARCHAR(10), C5 VARCHAR(10));
INSERT INTO @TEMP(C1, C2, C3, C4, C5) VALUES
('row1', 'data', 'data', 'name1', 'job'),
('row1', 'data', 'data', 'name2', 'job'),
('row2', 'data', 'data', 'name3', 'job'),
('row2', 'data', 'data', 'name4', 'job');

; WITH CTE AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY C1, C2, C3 Order by C4) as RN FROM @TEMP)
SELECT DISTINCT T1.C1, T1.C2, T1.C3, (CASE WHEN T1.RN = 1 THEN T1.C4 END) as C4,
T1.C5, (CASE WHEN T2.RN = 2 THEN T2.C4 END) as C6
FROM CTE T1 CROSS JOIN CTE T2 where T1.C1 = T2.C1 and T1.C2 = T2.C2 and
T1.C3 = T2.C3 and T1.RN = T2.RN - 1;

[/CODE]

alhakimi
Starting Member

23 Posts

Posted - 2013-05-22 : 22:09:09

Hi Mumu

thank you for your reply, but still missing some thing, if i have more than two rows like
row1 data data name1 job
row1 data data name2 job
row1 data data name3 job
row1 data data name4 job
row1 data data name5 job
row2 data data name3 job
row2 data data name4 job

for example some time i can have 4 rows and some time three rows
thank you .
also c4 can be repeated value it can avaliable in differnet rows again and again with differnet information in columns 1 2 3 5
Regards

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-23 : 10:38:49

Not the most elegant or efficient way but gets you what you want:
[CODE]
DECLARE @TEMP TABLE(C1 VARCHAR(10), C2 VARCHAR(10), C3 VARCHAR(10), C4 VARCHAR(10), C5 VARCHAR(10));
INSERT INTO @TEMP(C1, C2, C3, C4, C5) VALUES
('row1', 'data', 'data', 'name1', 'job'),
('row1', 'data', 'data', 'name2', 'job'),
('row1', 'data', 'data', 'name3', 'job'),
('row1', 'data', 'data', 'name4', 'job'),
('row2', 'data', 'data', 'name3', 'job'),
('row2', 'data', 'data', 'name4', 'job'),
('row2', 'data', 'data', 'name5', 'job'),
('row3', 'data', 'data', 'name4', 'job'),
('row3', 'data', 'data', 'name3', 'job'),
('row4', 'data', 'data', 'name4', 'job');

; WITH CTE AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY C1, C2, C3 Order by C4) as RN FROM @TEMP),
CTE1 AS
(SELECT DISTINCT T1.C1, T1.C2, T1.C3, (CASE WHEN T1.RN = 1 THEN T1.C4 END) as C4,
T1.C5, (CASE WHEN T2.RN = 2 THEN T2.C4 END) as C6
FROM CTE T1 CROSS JOIN CTE T2 where T1.C1 = T2.C1 and T1.C2 = T2.C2 and
T1.C3 = T2.C3 and T1.RN = T2.RN - 1 and
T1.RN = 1),
CTE2 AS
(SELECT T1.C1, T1.C2, T1.C3, (CASE WHEN T1.RN = 1 THEN T1.C4 END) as C4,
T1.C5, (CASE WHEN T2.RN = 2 THEN T2.C4 END) as C6,
(CASE WHEN T3.RN = 3 THEN T3.C4 END) as C7
FROM CTE T1 CROSS JOIN CTE T2 CROSS JOIN CTE T3 WHERE
T1.C1 = T2.C1 and T1.C2 = T2.C2 and
T1.C3 = T2.C3 and T1.RN = T2.RN - 1 and
T1.C1 = T2.C1 and T1.C2 = T2.C2 and
T1.C3 = T2.C3 and T1.RN = T2.RN - 1 and
T1.C1 = T3.C1 and T1.C2 = T3.C2 and
T1.C3 = T3.C3 and T1.RN = T3.RN - 2 and
T1.RN = 1),
CTE3 AS
(SELECT T1.C1, T1.C2, T1.C3, (CASE WHEN T1.RN = 1 THEN T1.C4 END) as C4,
T1.C5, (CASE WHEN T2.RN = 2 THEN T2.C4 END) as C6,
(CASE WHEN T3.RN = 3 THEN T3.C4 END) as C7,
(CASE WHEN T4.RN = 4 THEN T4.C4 END) as C8
FROM CTE T1 CROSS JOIN CTE T2 CROSS JOIN CTE T3 CROSS JOIN CTE T4 WHERE
T1.C1 = T2.C1 and T1.C2 = T2.C2 and
T1.C3 = T2.C3 and T1.RN = T2.RN - 1 and
T1.C1 = T2.C1 and T1.C2 = T2.C2 and
T1.C3 = T2.C3 and T1.RN = T2.RN - 1 and
T1.C1 = T3.C1 and T1.C2 = T3.C2 and
T1.C3 = T3.C3 and T1.RN = T3.RN - 2 and
T1.C1 = T4.C1 and T1.C2 = T4.C2 and
T1.C3 = T4.C3 and T1.RN = T4.RN - 3 and
T1.RN = 1)
SELECT T11.C1, T11.C2, T11.C3, T11.C4, T22.C5, T22.C6, T33.C7, T44.C8 from
CTE T11 LEFT JOIN CTE1 T22 ON T11.C1 = T22.C1
LEFT JOIN CTE2 T33 ON T11.C1 = T33.C1 LEFT JOIN CTE3 T44 ON T11.C1 = T44.C1
WHERE T11.RN = 1;

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 00:44:33

if you're sure on maximum number of records that can come for a group you can do this


SELECT C1,C2,C3,
MAX(CASE WHEN Seq=1 THEN C4 END) AS C4,
MAX(CASE WHEN Seq=1 THEN C5 END) AS C5,
MAX(CASE WHEN Seq=2 THEN C4 END) AS C6,
MAX(CASE WHEN Seq=2 THEN C5 END) AS C7,
...
MAX(CASE WHEN Seq=5 THEN C4 END) AS C13,
MAX(CASE WHEN Seq=5 THEN C5 END) AS C14,
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY C1, c2, C3 ORDER BY C4) AS Seq,*
FROM Table
)t
GROUP BY C1,C2,C3

if you cant determine it at compile time you need this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-24 : 11:24:51

That is a much better solution Visakh.
Nice job.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-25 : 09:17:21

quote:
Originally posted by MuMu88

That is a much better solution Visakh.
Nice job.

Thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources