Rows to Columns - 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

Rows to Columns

Author

Topic

diogenes
Starting Member

6 Posts

Posted - 2011-05-30 : 13:00:10

Hi,
I have a table with 4 columns

Name Date Value idx
Lucy 12-20-2010 10 1
Lucy 12-20-2010 15 2
Lucy 12-20-2010 13 3
Lucy 12-20-2010 12 4
Luke 12-20-2010 10 1
Luke 12-20-2010 15 2
Luke 12-20-2010 13 3
Luke 12-20-2010 18 4

I want to diplay this table as

Name Date idx1 idx2 idx3 idx4
Lucy 12-20-2010 10 15 13 12
Luke 12-20-2010 10 15 13 18

Any help is appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-30 : 13:44:44

You can use PIVOT or CASE. This looks like homework, so show us what you've come up with so far and we'll help get the rest of the way.

Jim

Everyday I learn something that somebody else already knew

diogenes
Starting Member

6 Posts

Posted - 2011-05-30 : 14:05:42

It's not a homework
I just used that exemple to simplified the problem.

Actually, my table looks like this:

Date Pin Time IDX
2011-03-01 010071125296 07:00:00 1
2011-03-01 010071125296 08:10:00 2
2011-03-01 010071125296 09:07:00 3
2011-03-01 010071125296 10:08:00 4

I tried to use pivod and that's what I got:

SELECT Date,Pin,[1],[2],[3],[4],[5],[6]
FROM ( SELECT idx,pin,date,time from @MyTable) as tb
pivot(min(time) for idx in ([1],[2],[3],[4],[5],[6])) as pv

Date Pin 1 2 3 4
2011-03-01 010071125296 07:00:00 NULL NULL NULL
2011-03-01 010071125296 NULL 08:10:00 NULL NULL
2011-03-01 010071125296 NULL NULL 09:07:00 NULL
2011-03-01 010071125296 NULL NULL NULL 10:08:00

how can I display it in one line?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-30 : 14:53:50

I don't see anything wrong with your code. Here is an example that works correctly. It is using your query exactly as it is (except I changed the table name to #tmp). See if this works and compare with your code.


create table #tmp ([date] date, pin varchar(255), [time] time, idx int);

insert into #tmp values 
('2011-03-01','010071125296','07:00:00','1'),
('2011-03-01','010071125296','08:10:00','2'),
('2011-03-01','010071125296','09:07:00','3'),
('2011-03-01','010071125296','10:08:00','4')

SELECT Date,Pin,[1],[2],[3],[4],[5],[6]
FROM ( SELECT idx,pin,date,time from #tmp) as tb
pivot(min(time) for idx in ([1],[2],[3],[4],[5],[6])) as pv

drop table #tmp;

If you are using SQL 2005 or lower, change the data types to datetime instead of date and time.

diogenes
Starting Member

6 Posts

Posted - 2011-05-30 : 15:05:29

the following code works perfectly:

select Date,Pin,
MIN(CASE WHEN idx = 1 then time ELSE NULL END) AS t1,
MIN(CASE WHEN idx = 2 then time ELSE NULL END) AS t2,
MIN(CASE WHEN idx = 3 then time ELSE NULL END) AS t3,
MIN(CASE WHEN idx = 4 then time ELSE NULL END) AS t4,
MIN(CASE WHEN idx = 5 then time ELSE NULL END) AS t5,
MIN(CASE WHEN idx = 6 then time ELSE NULL END) AS t6
FROM MyTable
group by Date,pin

thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-30 : 15:25:14

SO does Sunita's. You may find a performance hit in your way over Sunita's, but hopefully not of any significance.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources