Select 3 records and display it as a single record

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select 3 records and display it as a single record

Author

Topic

kka_anand
Starting Member

24 Posts

Posted - 2010-08-07 : 14:24:58

Hi All,

I have a requirement to select 3/n no of records and display it as a single record.

for e.g - 3 fields table with 3 records
-------
aa 12 35
bb 100 1
cc 22 10

this 3 records should be in this format

aa 12 35 bb 100 1 cc 22 10

Can anyone help me out to do it using T-Sql.

Thanks in advance!!!

Regards
Anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-08 : 02:24:03

something like


SELECT MAX(CASE WHEN seq=1 THEN field1 ELSE NULL END) AS field1,
MAX(CASE WHEN seq=1 THEN field2 ELSE NULL END) AS field2,
MAX(CASE WHEN seq=1 THEN field3 ELSE NULL END) AS field3,
MAX(CASE WHEN seq=2 THEN field1 ELSE NULL END) AS field1,
MAX(CASE WHEN seq=2 THEN field2 ELSE NULL END) AS field2,
MAX(CASE WHEN seq=2 THEN field3 ELSE NULL END) AS field3,
MAX(CASE WHEN seq=3 THEN field1 ELSE NULL END) AS field1,
MAX(CASE WHEN seq=3 THEN field2 ELSE NULL END) AS field2,
MAX(CASE WHEN seq=3 THEN field3 ELSE NULL END) AS field3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY (rn-1)/3 ORDER BY rn) AS seq,(rn-1)/3 AS rowno,other columns...
FROM
(
SELECT ROW_NUMBER() OVER ( ORDER BY field1) AS rn,*
FROM Table
)t
)r
GROUP BY (rn-1)/3

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

anaxy
Starting Member

1 Post

Posted - 2010-08-08 : 12:17:38

Use FULL JOIN for this requirement

kka_anand
Starting Member

24 Posts

Posted - 2010-08-08 : 15:43:13

Thanks for the post. Let me try!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-09 : 04:55:53

quote:
Originally posted by anaxy

Use FULL JOIN for this requirement

Why?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-10 : 14:22:20

quote:
Originally posted by anaxy

Use FULL JOIN for this requirement

how?

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

kka_anand
Starting Member

24 Posts

Posted - 2010-08-13 : 16:25:52

Hi visakh,

I have tried your query, it throws an error message at GROUP BY clause. If I comment the Group by clause, it displays one the 1st row. Please advice

Msg 207, Level 16, State 1, Line 54
Invalid column name 'rn'.

Regards
Anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 01:26:30

quote:
Originally posted by kka_anand

Hi visakh,

I have tried your query, it throws an error message at GROUP BY clause. If I comment the Group by clause, it displays one the 1st row. Please advice

Msg 207, Level 16, State 1, Line 54
Invalid column name 'rn'.

Regards
Anand

sorry it was a typo


SELECT MAX(CASE WHEN seq=1 THEN field1 ELSE NULL END) AS field1,
MAX(CASE WHEN seq=1 THEN field2 ELSE NULL END) AS field2,
MAX(CASE WHEN seq=1 THEN field3 ELSE NULL END) AS field3,
MAX(CASE WHEN seq=2 THEN field1 ELSE NULL END) AS field1,
MAX(CASE WHEN seq=2 THEN field2 ELSE NULL END) AS field2,
MAX(CASE WHEN seq=2 THEN field3 ELSE NULL END) AS field3,
MAX(CASE WHEN seq=3 THEN field1 ELSE NULL END) AS field1,
MAX(CASE WHEN seq=3 THEN field2 ELSE NULL END) AS field2,
MAX(CASE WHEN seq=3 THEN field3 ELSE NULL END) AS field3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY (rn-1)/3 ORDER BY rn) AS seq,(rn-1)/3 AS rowno,other columns...
FROM
(
SELECT ROW_NUMBER() OVER ( ORDER BY field1) AS rn,*
FROM Table
)t
)r
GROUP BY (rn-1)/3 rowno

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

kka_anand
Starting Member

24 Posts

Posted - 2010-08-14 : 13:43:42

Thanks visakh,

It's woking fine.

As per the requirement, the records are partioned into 3 and displaying as a single record.
I have another problem. If you see the below result, there are only 2 records exists for "123", because of that it is picking the "ABCDEF" as a 3 to partion causing the problem.
To avoid this, I need to filter the condition to allow the record count that are equal to 3.

1 0 ABC
2 0 ABC
3 0 ABC

4 1 123
5 1 123
6 2 ABCDEF

7 2 ABCDEF
8 2 ABCDEF
9 4 434

If we add a filter then, it look like this (skips "123" record)

1 0 ABC
2 0 ABC
3 0 ABC

6 2 ABCDEF
7 2 ABCDEF
8 2 ABCDEF

9 4 434
...

Now this is my requirement. How to add a filter condtion?

Thanks in Advance....

Regards
Anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 13:50:06

[code]
SELECT MAX(CASE WHEN seq=1 THEN field1 ELSE NULL END) AS field1,
MAX(CASE WHEN seq=1 THEN field2 ELSE NULL END) AS field2,
MAX(CASE WHEN seq=1 THEN field3 ELSE NULL END) AS field3,
MAX(CASE WHEN seq=2 THEN field1 ELSE NULL END) AS field1,
MAX(CASE WHEN seq=2 THEN field2 ELSE NULL END) AS field2,
MAX(CASE WHEN seq=2 THEN field3 ELSE NULL END) AS field3,
MAX(CASE WHEN seq=3 THEN field1 ELSE NULL END) AS field1,
MAX(CASE WHEN seq=3 THEN field2 ELSE NULL END) AS field2,
MAX(CASE WHEN seq=3 THEN field3 ELSE NULL END) AS field3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY (rn-1)/3 ORDER BY rn) AS seq,(rn-1)/3 AS rowno,other columns...
FROM
(
SELECT ROW_NUMBER() OVER ( ORDER BY field1) AS rn,COUNT(1) OVER (PARTITION BY field3) AS Occur,*
FROM Table
)t
WHERE Occur =3
)r
GROUP BY rowno

[/code]

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

kka_anand
Starting Member

24 Posts

Posted - 2010-08-14 : 14:54:58

Thaks SS

I have applied your comments, but still the same error. If you see the 'ASTRAL' value the rowno is collapsing (1 2 1 2 3).

Occurs Seq rowno Date Name
-------------------------------------------------------------------
5 5 96 2010-07-26 00:00:00 ASTEC
5 1 97 2010-07-30 00:00:00 ASTRAL
5 2 97 2010-07-29 00:00:00 ASTRAL
5 1 98 2010-07-28 00:00:00 ASTRAL
5 2 98 2010-07-27 00:00:00 ASTRAL
5 3 98 2010-07-26 00:00:00 ASTRAL
5 4 98 2010-07-30 00:00:00 MICRO
5 5 98 2010-07-29 00:00:00 MICRO

Thanks
Anand

kka_anand
Starting Member

24 Posts

Posted - 2010-08-14 : 15:57:29

Hi Visakh,

I have used 5 as PARTITION and the result looks like this.
If you see the 'ASTRAL' and 'MICRO' the rownos are collapsing (1 2 1 2 3).

Occurs Seq rowno Date Name
-------------------------------------------------------------------
5 1 96 2010-07-30 00:00:00 ASTEC
5 2 96 2010-07-29 00:00:00 ASTEC
5 3 96 2010-07-28 00:00:00 ASTEC
5 4 96 2010-07-27 00:00:00 ASTEC
5 5 96 2010-07-26 00:00:00 ASTEC
5 1 97 2010-07-30 00:00:00 ASTRAL
5 2 97 2010-07-29 00:00:00 ASTRAL
5 1 98 2010-07-28 00:00:00 ASTRAL
5 2 98 2010-07-27 00:00:00 ASTRAL
5 3 98 2010-07-26 00:00:00 ASTRAL
5 4 98 2010-07-30 00:00:00 MICRO
5 5 98 2010-07-29 00:00:00 MICRO
5 1 99 2010-07-28 00:00:00 MICRO
5 2 99 2010-07-27 00:00:00 MICRO
5 3 99 2010-07-26 00:00:00 MICRO

Final Result -- ASTEC is correct, others (ASTRAL & MICRO) are incorrect.
============
2010-07-30 ASTEC 2010-07-29 ASTEC 2010-07-28 ASTEC 2010-07-27 ASTEC 2010-07-26 ASTEC

2010-07-30 ASTRAL 2010-07-29 ASTRAL NULL NULL NULL NULL NULL NULL
2010-07-28 ASTRAL 2010-07-27 ASTRAL 2010-07-26 ASTRAL NULL NULL NULL NULL

NULL NULL NULL NULL NULL NULL 2010-07-30 MICRO 2010-07-29 MICRO
2010-07-29 MICRO

Thanks
Anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-15 : 03:14:52

may be this?


SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY rowno) AS Seq, *
FROM
(
SELECT (rn-1)/3 AS rowno,other columns...
FROM
(
SELECT ROW_NUMBER() OVER ( ORDER BY field1) AS rn,COUNT(1) OVER (PARTITION BY field3) AS Occur,*
FROM Table
)t
)r
)p

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

kka_anand
Starting Member

24 Posts

Posted - 2010-08-15 : 06:41:45

Hi Visakh,

It Works fine. You are Great.
Thanks a lot for your valuable time and support.

Regards
Anand

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources