Query - 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

Query

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-04-20 : 06:45:41

I have a data in the table as below

[TAB1] ( col1,col2,col3)



Col1	Col2 	Col3
1	A	ab1
1	A	xy1
2	B	ab3
2	B	xy4
3	C	12a
3	C	a23
4	D	abc
4	D	cde
4	D	fgh
5	E	p1
5	E	p2
5	E	p3
5	E	p4


I want output as below, I can add the Act1.. Act4 column in other table and populate it

Col1 	Col2	Act1	Act2	Act3	Act4
1	A	ab1	xy1		
2	B	ab3	xy4		
3	C	12a	a23		
4	D	abc	cde	fgh	
5	E	p1	p2	p3	p4

-Neil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-20 : 07:04:17

[code]DECLARE @Sample TABLE
(
Col1 INT,
Col2 CHAR(1),
Col3 VARCHAR(3)
)

INSERT @Sample
VALUES (1, 'A', 'ab1'),
(1, 'A', 'xy1'),
(2, 'B', 'ab3'),
(2, 'B', 'xy4'),
(3, 'C', '12a'),
(3, 'C', 'a23'),
(4, 'D', 'abc'),
(4, 'D', 'cde'),
(4, 'D', 'fgh'),
(5, 'E', 'p1'),
(5, 'E', 'p2'),
(5, 'E', 'p3'),
(5, 'E', 'p1'),
(9, 'z', 'p2'),
(9, 'z', 'p3'),
(9, 'z', 'p4'),
(9, 'z', 'p5'),
(9, 'z', 'p6'),
(9, 'z', 'p7')

-- SwePeso
;WITH cteSource(Col1, Col2, rn, Col3)
AS (
SELECT Col1,
Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col3) - 1 AS rn,
Col3
FROM @Sample
)
SELECT Col1,
Col2,
MAX(CASE WHEN rn % 4 = 0 THEN Col3 ELSE NULL END) AS Act1,
MAX(CASE WHEN rn % 4 = 1 THEN Col3 ELSE NULL END) AS Act2,
MAX(CASE WHEN rn % 4 = 2 THEN Col3 ELSE NULL END) AS Act3,
MAX(CASE WHEN rn % 4 = 3 THEN Col3 ELSE NULL END) AS Act4
FROM cteSource
GROUP BY Col1,
Col2,
rn / 4
ORDER BY Col1,
Col2,
rn / 4[/code]

N 56°04'39.26"
E 12°55'05.63"

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-04-20 : 08:10:19

Thanks Peso,

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 14:52:11

are you sure you will have maximum of 4 values always? In case there are more than 4,do you want all of them or just first 4 among them?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-20 : 15:19:44

My query takes care of that, it populates as many rows necessary.
If you don't want that, add "WHERE rn <= 3" to the outermost query.

N 56°04'39.26"
E 12°55'05.63"

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-04-21 : 21:34:02

Hi Visakh,

There can be more than 4, but right now I am seeing only 4. Actually it was in the excel which I imported to SQL and then trying to write a query on this to clean it as per business rule.

There are around 3K+ records to be cleaned in this way.

Regards,

quote:
Originally posted by visakh16

are you sure you will have maximum of 4 values always? In case there are more than 4,do you want all of them or just first 4 among them?

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

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-04-22 : 02:31:01

Hi Peso,

Assuming that data is the physical table how this query needs to be rewritten?

quote:

Originally posted by SwePeso

DECLARE	@Sample TABLE
	(
		Col1 INT,
		Col2 CHAR(1),
		Col3 VARCHAR(3)
	)

INSERT	@Sample
VALUES	(1, 'A', 'ab1'),
	(1, 'A', 'xy1'),
	(2, 'B', 'ab3'),
	(2, 'B', 'xy4'),
	(3, 'C', '12a'),
	(3, 'C', 'a23'),
	(4, 'D', 'abc'),
	(4, 'D', 'cde'),
	(4, 'D', 'fgh'),
	(5, 'E', 'p1'),
	(5, 'E', 'p2'),
	(5, 'E', 'p3'),
	(5, 'E', 'p1'),
	(9, 'z', 'p2'),
	(9, 'z', 'p3'),
	(9, 'z', 'p4'),
	(9, 'z', 'p5'),
	(9, 'z', 'p6'),
	(9, 'z', 'p7')

-- SwePeso
;WITH cteSource(Col1, Col2, rn, Col3)
AS (
	SELECT	Col1,
		Col2,
		ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col3) - 1 AS rn,
		Col3
	FROM	@Sample
)
SELECT		Col1,
		Col2,
		MAX(CASE WHEN rn % 4 = 0 THEN Col3 ELSE NULL END) AS Act1,
		MAX(CASE WHEN rn % 4 = 1 THEN Col3 ELSE NULL END) AS Act2,
		MAX(CASE WHEN rn % 4 = 2 THEN Col3 ELSE NULL END) AS Act3,
		MAX(CASE WHEN rn % 4 = 3 THEN Col3 ELSE NULL END) AS Act4
FROM		cteSource
GROUP BY	Col1,
		Col2,
		rn / 4
ORDER BY	Col1,
		Col2,
		rn / 4

N 56°04'39.26"
E 12°55'05.63"

-Neil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-22 : 03:08:05

[code]-- SwePeso
;WITH cteSource(Col1, Col2, rn, Col3)
AS (
SELECT Col1,
Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col3) - 1 AS rn,
Col3
FROM {Your table name here}
)
SELECT Col1,
Col2,
MAX(CASE WHEN rn % 4 = 0 THEN Col3 ELSE NULL END) AS Act1,
MAX(CASE WHEN rn % 4 = 1 THEN Col3 ELSE NULL END) AS Act2,
MAX(CASE WHEN rn % 4 = 2 THEN Col3 ELSE NULL END) AS Act3,
MAX(CASE WHEN rn % 4 = 3 THEN Col3 ELSE NULL END) AS Act4
FROM cteSource
GROUP BY Col1,
Col2,
rn / 4
ORDER BY Col1,
Col2,
rn / 4[/code]

N 56°04'39.26"
E 12°55'05.63"

huum
Starting Member

9 Posts

Posted - 2012-04-22 : 21:42:22

Exprting query to Excel with headers through sql script. Any one?

[k Shaz---Torontonian]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 21:47:40

dont hijack threads. please post it as a new thread

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-04-23 : 02:55:55

Thanks Peso

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources