SQL Result - 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

SQL Result

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-06-03 : 03:26:19

Is this doable in SQL? If yes, how?

thanks in advance


FROM THIS FORMAT			TO THIS FORMAT					
ID	NAME		ID	NAME	ID	NAME	ID	NAME
1	ABC		1	ABC	4	A1B	7	MNO
2	DEF		2	DEF	5	XYZ		
3	ACB		3	ACB	6	QWE		
4	A1B							
5	XYZ							
6	QWE							
7	MNO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 03:48:31

it can be done
see


SELECT MAX(CASE WHEN ID%3 = 1 THEN ID END) AS ID1,
 MAX(CASE WHEN ID%3 = 1 THEN ID END) AS Name1,
 MAX(CASE WHEN ID%3 = 2 THEN ID END) AS ID2,
 MAX(CASE WHEN ID%3 = 2 THEN ID END) AS Name2,
 MAX(CASE WHEN ID%3 = 0 THEN ID END) AS ID3,
 MAX(CASE WHEN ID%3 = 0 THEN ID END) AS Name3
FROM table
GROUP BY (ID-1)/3

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

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-06-03 : 04:15:17

It works but the result is like this:


ID	NAME	ID	NAME	ID	NAME
1	ABC	2	DEF	3	ACB
4	A1B	5	XYZ	6	QWE	
7	MNO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 04:23:18

oh you want it other way around?
ok here you go


Posted - 06/03/2013 :  03:48:31           
--------------------------------------------------------------------------------
 
it can be done
see

[code]
SELECT MAX(CASE WHEN Seq = 1 THEN ID END) AS ID1,
 MAX(CASE WHEN Seq  = 1 THEN ID END) AS Name1,
 MAX(CASE WHEN Seq  = 2 THEN ID END) AS ID2,
 MAX(CASE WHEN Seq  = 2 THEN ID END) AS Name2,
 MAX(CASE WHEN Seq  = 3 THEN ID END) AS ID3,
 MAX(CASE WHEN Seq  = 3 THEN ID END) AS Name3
FROM ( SELECT ROW_NUMBER OVER (PARTITION BY ID%3 ORDER BY ID) AS Seq,*
FROM table)t
GROUP BY ID%3
ORDER BY COALESCE(NULLIF(ID%3,0),3) ASC,Seq ASC

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-03 : 04:38:05

ORDER BY doesn't allow the columns that are not in SELECT clause..
So simply you can Order result set by ID1
SELECT MAX(CASE WHEN Seq = 1 THEN ID END) AS ID1,
MAX(CASE WHEN Seq = 1 THEN ID END) AS Name1,
MAX(CASE WHEN Seq = 2 THEN ID END) AS ID2,
MAX(CASE WHEN Seq = 2 THEN ID END) AS Name2,
MAX(CASE WHEN Seq = 3 THEN ID END) AS ID3,
MAX(CASE WHEN Seq = 3 THEN ID END) AS Name3
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY ID%3 ORDER BY ID) AS Seq,*
FROM @tab)t
GROUP BY ID%3
ORDER BY ID1

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 04:43:15

quote:
Originally posted by bandi

ORDER BY doesn't allow the columns that are not in SELECT clause..
So simply you can Order result set by ID1
SELECT MAX(CASE WHEN Seq = 1 THEN ID END) AS ID1,
MAX(CASE WHEN Seq = 1 THEN ID END) AS Name1,
MAX(CASE WHEN Seq = 2 THEN ID END) AS ID2,
MAX(CASE WHEN Seq = 2 THEN ID END) AS Name2,
MAX(CASE WHEN Seq = 3 THEN ID END) AS ID3,
MAX(CASE WHEN Seq = 3 THEN ID END) AS Name3
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY ID%3 ORDER BY ID) AS Seq,*
FROM @tab)t
GROUP BY ID%3
ORDER BY ID1

--
Chandu

Hmm...thats not true. Thats only when DISTINCT is applied which is not the case here

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-03 : 04:54:08

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

ORDER BY doesn't allow the columns that are not in SELECT clause..
So simply you can Order result set by ID1
SELECT MAX(CASE WHEN Seq = 1 THEN ID END) AS ID1,
MAX(CASE WHEN Seq = 1 THEN ID END) AS Name1,
MAX(CASE WHEN Seq = 2 THEN ID END) AS ID2,
MAX(CASE WHEN Seq = 2 THEN ID END) AS Name2,
MAX(CASE WHEN Seq = 3 THEN ID END) AS ID3,
MAX(CASE WHEN Seq = 3 THEN ID END) AS Name3
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY ID%3 ORDER BY ID) AS Seq,*
FROM @tab)t
GROUP BY ID%3
ORDER BY ID1

--
Chandu

Hmm...thats not true. Thats only when DISTINCT is applied which is not the case here

Hi visakh,
here the case is with GROUP BY clause...

Msg 8127, Level 16, State 1, Line 20
Column "t.Seq" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

--
Chandu

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-06-03 : 04:57:05

Here is my SQL:

SELECT MAX(CASE WHEN Seq = 1 THEN PEMESubTypeID END) AS PEMESubTypeID1,
 MAX(CASE WHEN Seq = 1 THEN PEMESubTypeName END) AS PEMESubTypeName1,
 MAX(CASE WHEN Seq = 2 THEN PEMESubTypeID END) AS PEMESubTypeID2,
 MAX(CASE WHEN Seq = 2 THEN PEMESubTypeName END) AS PEMESubTypeName2,
 MAX(CASE WHEN Seq = 0 THEN PEMESubTypeID END) AS PEMESubTypeID3,
 MAX(CASE WHEN Seq = 0 THEN PEMESubTypeName END) AS PEMESubTypeName3
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY PEMESubTypeID%3 ORDER BY PEMESubTypeID) AS Seq,*
FROM PEMESubType WHERE PEMETypeID = 1)t
GROUP BY PEMESubTypeID%3, t.Seq
ORDER BY COALESCE(NULLIF(PEMESubTypeID%3,0),3) ASC,Seq ASC

Result:

PEMESubTypeID1	PEMESubTypeName1	PEMESubTypeID2	PEMESubTypeName2	PEMESubTypeID3	PEMESubTypeName3
1	Head/Neck Injury	NULL	NULL	NULL	NULL
NULL	NULL	4	Fainting Spells, Fits, Seizures	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL

how to remove those null?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 04:58:58

quote:

Originally posted by chriztoph

Here is my SQL:

SELECT MAX(CASE WHEN Seq = 1 THEN PEMESubTypeID END) AS PEMESubTypeID1,
 MAX(CASE WHEN Seq = 1 THEN PEMESubTypeName END) AS PEMESubTypeName1,
 MAX(CASE WHEN Seq = 2 THEN PEMESubTypeID END) AS PEMESubTypeID2,
 MAX(CASE WHEN Seq = 2 THEN PEMESubTypeName END) AS PEMESubTypeName2,
 MAX(CASE WHEN Seq = 0 THEN PEMESubTypeID END) AS PEMESubTypeID3,
 MAX(CASE WHEN Seq = 0 THEN PEMESubTypeName END) AS PEMESubTypeName3
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY PEMESubTypeID%3 ORDER BY PEMESubTypeID) AS Seq,*
FROM PEMESubType WHERE PEMETypeID = 1)t
GROUP BY PEMESubTypeID%3, t.Seq
ORDER BY COALESCE(NULLIF(PEMESubTypeID%3,0),3) ASC,Seq ASC

Result:

PEMESubTypeID1	PEMESubTypeName1	PEMESubTypeID2	PEMESubTypeName2	PEMESubTypeID3	PEMESubTypeName3
1	Head/Neck Injury	NULL	NULL	NULL	NULL
NULL	NULL	4	Fainting Spells, Fits, Seizures	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL

how to remove those null?

you can use ISNULL or COALESCE for that

but my question is why you've so many missing values?
is this expected?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 05:00:12

quote:
Originally posted by bandi

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

ORDER BY doesn't allow the columns that are not in SELECT clause..
So simply you can Order result set by ID1
SELECT MAX(CASE WHEN Seq = 1 THEN ID END) AS ID1,
MAX(CASE WHEN Seq = 1 THEN ID END) AS Name1,
MAX(CASE WHEN Seq = 2 THEN ID END) AS ID2,
MAX(CASE WHEN Seq = 2 THEN ID END) AS Name2,
MAX(CASE WHEN Seq = 3 THEN ID END) AS ID3,
MAX(CASE WHEN Seq = 3 THEN ID END) AS Name3
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY ID%3 ORDER BY ID) AS Seq,*
FROM @tab)t
GROUP BY ID%3
ORDER BY ID1

--
Chandu

Hmm...thats not true. Thats only when DISTINCT is applied which is not the case here

Hi visakh,
here the case is with GROUP BY clause...

Msg 8127, Level 16, State 1, Line 20
Column "t.Seq" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

--
Chandu

Dont get confused
this is not problem with order by but its because grouping is on different level
It was actually a typo, forgot taking out Seq from ORDER by after i added those MAX statements

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-03 : 05:01:53

quote:

Originally posted by chriztoph

Here is my SQL:

SELECT MAX(CASE WHEN Seq = 1 THEN PEMESubTypeID END) AS PEMESubTypeID1,
 MAX(CASE WHEN Seq = 1 THEN PEMESubTypeName END) AS PEMESubTypeName1,
 MAX(CASE WHEN Seq = 2 THEN PEMESubTypeID END) AS PEMESubTypeID2,
 MAX(CASE WHEN Seq = 2 THEN PEMESubTypeName END) AS PEMESubTypeName2,
 MAX(CASE WHEN Seq = 0 THEN PEMESubTypeID END) AS PEMESubTypeID3,
 MAX(CASE WHEN Seq = 0 THEN PEMESubTypeName END) AS PEMESubTypeName3
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY PEMESubTypeID%3 ORDER BY PEMESubTypeID) AS Seq,*
FROM PEMESubType WHERE PEMETypeID = 1)t
GROUP BY PEMESubTypeID%3, t.Seq
ORDER BY COALESCE(NULLIF(PEMESubTypeID%3,0),3) ASC,Seq ASC

Result:

PEMESubTypeID1	PEMESubTypeName1	PEMESubTypeID2	PEMESubTypeName2	PEMESubTypeID3	PEMESubTypeName3
1	Head/Neck Injury	NULL	NULL	NULL	NULL
NULL	NULL	4	Fainting Spells, Fits, Seizures	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL

how to remove those null?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 05:02:41

this is another way to avoid null values


SELECT MAX(CASE WHEN Seq = 1 THEN PEMESubTypeID ELSE 0 END) AS PEMESubTypeID1,
 MAX(CASE WHEN Seq = 1 THEN PEMESubTypeName ELSE '' END) AS PEMESubTypeName1,
 MAX(CASE WHEN Seq = 2 THEN PEMESubTypeID ELSE 0 END) AS PEMESubTypeID2,
 MAX(CASE WHEN Seq = 2 THEN PEMESubTypeName ELSE '' END) AS PEMESubTypeName2,
 MAX(CASE WHEN Seq = 0 THEN PEMESubTypeID ELSE 0 END) AS PEMESubTypeID3,
 MAX(CASE WHEN Seq = 0 THEN PEMESubTypeName ELSE '' END) AS PEMESubTypeName3
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY PEMESubTypeID%3 ORDER BY PEMESubTypeID) AS Seq,*
FROM PEMESubType WHERE PEMETypeID = 1)t
GROUP BY PEMESubTypeID%3
ORDER BY COALESCE(NULLIF(PEMESubTypeID%3,0),3) ASC

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-03 : 05:05:50

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

ORDER BY doesn't allow the columns that are not in SELECT clause..
So simply you can Order result set by ID1
SELECT MAX(CASE WHEN Seq = 1 THEN ID END) AS ID1,
MAX(CASE WHEN Seq = 1 THEN ID END) AS Name1,
MAX(CASE WHEN Seq = 2 THEN ID END) AS ID2,
MAX(CASE WHEN Seq = 2 THEN ID END) AS Name2,
MAX(CASE WHEN Seq = 3 THEN ID END) AS ID3,
MAX(CASE WHEN Seq = 3 THEN ID END) AS Name3
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY ID%3 ORDER BY ID) AS Seq,*
FROM @tab)t
GROUP BY ID%3
ORDER BY ID1

--
Chandu

Hmm...thats not true. Thats only when DISTINCT is applied which is not the case here

Hi visakh,
here the case is with GROUP BY clause...

Msg 8127, Level 16, State 1, Line 20
Column "t.Seq" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

--
Chandu

Dont get confused
this is not problem with order by but its because grouping is on different level
It was actually a typo, forgot taking out Seq from ORDER by after i added those MAX statements

Thanks visakh..
OP has kept the Seq in GROUP BY clause. that is the reason he got those NULLs there

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 05:07:09

Thanks visakh..
OP has kept the Seq in GROUP BY clause. that is the reason he got those NULLs there

--
Chandu
[/quote]
Anyways my latest suggestion should avoid any NULLs due to unavailability of values too

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

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-06-03 : 05:15:13

Thank you so much visakh16 and bandi. it works.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-03 : 05:16:25

quote:
Originally posted by chriztoph

Thank you so much visakh16 and bandi. it works.

welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 05:17:30

quote:
Originally posted by chriztoph

Thank you so much visakh16 and bandi. it works.

No problem
you're welcome

have a look at this to understand uses of row_number

http://beyondrelational.com/modules/2/blogs/70/posts/10802/multipurpose-rownumber-function.aspx

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