SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 multiple rows data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alhakimi
Starting Member

Yemen
23 Posts

Posted - 05/22/2013 :  17:12:31  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/22/2013 :  21:40:36  Show Profile  Reply with Quote
Here is a way to get what you want:


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;


Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 05/22/2013 :  22:09:09  Show Profile  Reply with Quote
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


Edited by - alhakimi on 05/22/2013 22:11:59
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/23/2013 :  10:38:49  Show Profile  Reply with Quote
Not the most elegant or efficient way but gets you what you want:

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;

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/24/2013 :  00:44:33  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/24/2013 :  11:24:51  Show Profile  Reply with Quote
That is a much better solution Visakh.
Nice job.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/25/2013 :  09:17:21  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000