How to shift many rows into one row, cross tab? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to shift many rows into one row, cross tab?

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 18:14:55

I have a table MyTable with data that looks like this.

MyTableID.......1ML.......2ML.......3ML.......4ML
....9......................1
....9..................................3
....9..............................................6
....9...........................................................2

I need it to look like this.

ColumnCount.......1ML.......2ML.......3ML.......4ML
....9..............1.........3.........6.........2

I am sure this is a common task. Is there a boiler plate piece of code I can use to do this? Or can some one point me in the right direction as to how to do it?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-13 : 18:44:42

GROUP BY keyword?

Poor planning on your part does not constitute an emergency on my part.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 20:50:42

If I group by MyTableID how do I get 1ML, 2ML, 3ML and 4ML into the output?

SELECT MyTableID, [1ML], [2ML], [3ML], [4ML] FROM MyTable GROUP BY MyTableID

Throws an error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-13 : 20:54:17

[code]
SELECT MyTableID, sum([1ML]), sum([2ML]), sum([3ML]), sum([4ML]) FROM MyTable GROUP BY MyTableID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 21:08:38

Oh, my bad.
1ML, 2ML, 3ML and 4ML are datatype varchar.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-13 : 21:15:58

change to MAX()

KH
[spoiler]Time is always against us[/spoiler]

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 21:24:41

That works great. Thank you!

I am curious why this works? The error said since the additional columns were not part of the group by it was an error. Yet it allows SUM and MAX. Why are those allowed?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-13 : 21:36:22

if you use GROUP BY, column not appearing in the GROUP BY clause need to apply an aggregate function on it.
BOL explain this better than me.

http://msdn2.microsoft.com/en-us/library/ms177673.aspx

KH
[spoiler]Time is always against us[/spoiler]

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 21:50:07

Ahhhh OK I see. Thanks for all the help. It is good to get a fish. But I always want to learn to fish. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-13 : 21:57:12

quote:
“Give a man a fish; you have fed him for today. Teach a man to fish; and you have fed him for a lifetime

KH
[spoiler]Time is always against us[/spoiler]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-13 : 22:17:50

quote:
Originally posted by khtan

quote:
“Give a man a fish; you have fed him for today. Teach a man to fish; and you have fed him for a lifetime

KH
[spoiler]Time is always against us[/spoiler]

quote:

There is a fine line between fishing and standing on the shore like an idiot.

Blue Collar Comedy Tour.

Poor planning on your part does not constitute an emergency on my part.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 22:27:43

this reminds me of a related newbie question I have concerning SELECT DISTINCT.

USE AdventureWorks;
GO
SELECT DISTINCT ProductID
FROM Production.ProductInventory

The above select will return a single column containing unique ProductID's. How can return the rest of the row for those unique ID's?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-13 : 22:45:29

Add the columns you want to the SELECT statement, if those columns are not as unique as the product ID, multiple rows per ProductID will be returned....

The DISTINCT will effectively make each row unique, depending on which columns you select..you may or may not get multiple (non unique) ProductID's

Poor planning on your part does not constitute an emergency on my part.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 23:08:27

OK I am clear with that. If I have data that looks like this.

ProductID………First_Name……Last_Name…..Major
1………………….Frank………….Johnson………Math
1………………….Frank………….Johnson…….English

SELECT DISTINCT ProductID, First_Name, Last_Name will return…

ProductID………First_Name……Last_Name
1………………….Frank………….Johnson

SELECT DISTINCT ProductID, First_Name, Last_Name, Major will return…

ProductID………First_Name……Last_Name…..Major
1………………….Frank………….Johnson………Math
1………………….Frank………….Johnson……..English

If I did not care what the major is I just want one listed, how could I get a return like…

ProductID………First_Name……Last_Name…..Major
1………………….Frank………….Johnson………Math

Distinct first and last with additional fields not part of the distinct?

My guess is something like this:

SELECT a.ProductID, a.First_Name, a.Last_Name, a.Major
FROM MyTable a
WHERE (SELECT DISTINCT ProductID, First_Name, Last_Name FROM MyTable) b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-13 : 23:17:22

One method to do that

SELECT t.ProductID,t.First_Name,t.Last_Name,t.Major 
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY ProductID,First_Name,Last_Name ORDER BY Major) AS RowNo,
ProductID,First_Name,Last_Name,Major
FROM Table) t 
WHERE t.RowNo=1

basically you need to give all fields based on which you take distinct inside PARTITION By and give other field after ORDER BY.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 23:31:40

WOW you just zoomed right past anything I know. I'll be spending some time studying your sample.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 00:05:42

Use this as a start off help. Has some usage examples also:-

[url]http://technet.microsoft.com/en-us/library/ms189461.aspx[/url]

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-14 : 02:26:19

quote:
Originally posted by kirknew2SQL

WOW you just zoomed right past anything I know. I'll be spending some time studying your sample.

Also see what you can do with ROW_NUMBER() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources