Display row value as 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

SQL Server 2005 Forums

Transact-SQL (2005)

Display row value as columns

Author

Topic

srkvellanki
Starting Member

14 Posts

Posted - 2009-02-22 : 15:00:12

Hi All,

I have a table with the columns empname,dateofbirth,group.An employee can be in different group during his service.

Data is as follows.

henry - 10/20/1960 - 2
henry - 10/20/1960 - 3
henry - 10/20/1960 - 5
steeve - 08/17/1965 - 8
steeve - 08/17/1965 - 10
steeve - 08/17/1965 - 9
steeve - 08/17/1965 - 4
steeve - 08/17/1965 - 2
Laura - 09/12/1967 -3

I need to display the data in a single row all groups as columns as below.

henry - 10/20/1960 - 2 - 3 - 5
steeve - 08/17/1965 - 8 -10-9-4-2
Laura - 09/12/1967 -3

Regards
Mac

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-22 : 18:13:44

You can use pivoting to get the data in the form you need to - something like this:

select empname,dateofbirth,[1],[2],[3],[4],[5],[6],[7],,[9],[10]
from YourTable
pivot
(
   max([group]) 
   for [group] in ([1],[2],[3],[4],[5],[6],[7],,[9],[10])
) as P

If you don't know the number of values of the groups, you cannot use this, instead you will need to use dynamic pivoting. Some examples of dynamic pivoting are here: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-22 : 18:24:09

If you need all the non-null values to be bunched up in the first few columns, you can modify the query I posted earlier to this:

with A as
(
select
   row_number() over (partition by empname, dateofbirth order by [group]) as rownum,
   empname,
   dateofbirth ,
   [group]
from YourTable
)
select empname, dateofbirth ,[1],[2],[3],[4],[5],[6],[7],,[9],[10]
from A
pivot
(
	max([group]) 
	for rownum in([1],[2],[3],[4],[5],[6],[7],,[9],[10])
) P

srkvellanki
Starting Member

14 Posts

Posted - 2009-02-22 : 20:15:17

Hi Sunita,

I want the record for an employee to be displayed once

Col1..Col5 = alias of column group

empname - dateofbirth-Col1-Col2-Col3-Col4-Col5

henry - 10/20/1960 -2 -3 -5 -null-null
steeve - 08/17/1965 -8 -10 -9 -4 -2
Laura - 09/12/1967 -3 -null-null-null-null

Regards
Mac

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-22 : 20:55:00

I think the second script I posted will give you exactly that - there will be only 3 rows, one for each employee+dateofbirth combination.

The order in which the group numbers are displayed for each employee is different from what you asked for in the example. This is because there is no information on how to order the groups - in my example, I ordered them by ascending group number.

Copy and paste this code and run it to test

declare @Employees table (empname varchar(32), dateofbirth datetime, [group] int);
insert into @Employees( empname, dateofbirth, [group]) values ('henry','10/20/1960',2);
insert into @Employees( empname, dateofbirth, [group]) values ('henry','10/20/1960',3);
insert into @Employees( empname, dateofbirth, [group]) values ('henry','10/20/1960',5);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve ','08/17/1965',8);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve ','08/17/1965',10);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve ','08/17/1965',9);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve ','08/17/1965',4);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve ','08/17/1965',2);
insert into @Employees( empname, dateofbirth, [group]) values ('Laura','09/12/1967',3);

with A as
(
select
   row_number() over (partition by empname, dateofbirth order by [group]) as rownum,
   empname,
   dateofbirth ,
   [group]
from @Employees
)
select empname, dateofbirth ,[1],[2],[3],[4],[5],[6],[7],"8",[9],[10]
from A
pivot
(
   max([group]) 
   for rownum in([1],[2],[3],[4],[5],[6],[7],"8",[9],[10])
) P

srkvellanki
Starting Member

14 Posts

Posted - 2009-02-22 : 21:55:47

Ms Sunita, Thank you very much for the post.A true comment from my side, you are a real genius

Best Regards
Mac

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-22 : 23:17:17

You can create function and use like this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=120268

srkvellanki
Starting Member

14 Posts

Posted - 2009-02-23 : 14:28:09

Sunita,

If one has to write a query without using "Pivot","Row_Number" how to go about it.

Regards
Mac

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 14:47:50

Then you have to use Cross tab method. Search there are millions of posts regarding cross tab .

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-23 : 19:57:13

The following query is a modified version of the one I posted earlier that does not use row_number and pivot. Can I ask why you don't want to use those features? If it is because you are on a SQL 2000 server, this query will not work either because SQL 2000 does not support common table expressions. But, the common table expression can be rewritten as a subquery.

declare @Employees table (empname varchar(32), dateofbirth datetime,[group] int);
insert into @Employees( empname, dateofbirth, [group]) values ('henry','10/20/1960',2);
insert into @Employees( empname, dateofbirth, [group]) values ('henry','10/20/1960',3);
insert into @Employees( empname, dateofbirth, [group]) values ('henry','10/20/1960',3);
insert into @Employees( empname, dateofbirth, [group]) values ('henry','10/20/1960',5);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve','08/17/1965',8);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve','08/17/1965',10);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve','08/17/1965',9);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve','08/17/1965',4);
insert into @Employees( empname, dateofbirth, [group]) values ('steeve','08/17/1965',2);
insert into @Employees( empname, dateofbirth, [group]) values('Laura','09/12/1967',3);

with A as
(
  select
    count(distinct b.[group]) as rownum,
    a.empname,
    a.dateofbirth ,
    a.[group]
  from 
    @Employees a cross join @Employees b
  where 
    a.empname=b.empname 
    and a.dateofbirth = b.dateofbirth 
    and  a.[group] >= b.[group]
  group by
    a.empname,
    a.dateofbirth ,
    a.[group]
)
select
   empname, dateofbirth ,
   max(case when rownum = 1 then [group] end ) as [1],
   max(case when rownum = 2 then [group] end ) as [2],
   max(case when rownum = 3 then [group] end ) as [3],
   max(case when rownum = 4 then [group] end ) as [4],
   max(case when rownum = 5 then [group] end ) as [5],
   max(case when rownum = 6 then [group] end ) as [6],
   max(case when rownum = 7 then [group] end ) as [7]
from 
   A
group by
   empname, dateofbirth

srkvellanki
Starting Member

14 Posts

Posted - 2009-02-23 : 20:31:13

Thank you Sunita

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 10:49:52

also if number of values for each group is not static use this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

hellosav
Starting Member

1 Post

Posted - 2009-04-13 : 09:20:00

Hi Sunita,

Thank you for the solution. I am facing a similar problem with respet to displaying 2 rows of information in the single row output. But here, unfortunately the row data type is "uniqueidentifier", not "int". This is the reason, your query is failing for me throwing an error that 'max() function cannot be applied to the datatype uniqueidentifier'. Please help

srkvellanki
Starting Member

14 Posts

Posted - 2009-10-08 : 11:31:34

Sunitha,

In a Interview i was asked this question.If a Query/SP is running slow how do you optimize it.Appreciate if you share detailed information.

Regards
Sri

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-10-08 : 16:01:19

quote:
Originally posted by srkvellanki

Sunitha,

In a Interview i was asked this question.If a Query/SP is running slow how do you optimize it.Appreciate if you share detailed information.

Regards
Sri

Step 1 would be to identify what specifically is causing the slow-down. If it was an SP, you could say you have a magic query which will identify the slowest query inside the SP:

SELECT TOP 200
     a.last_execution_time, 
     cast(last_elapsed_time/1000000.0 as decimal(10, 3)) Last_Elasped_Seconds,
	 (select name from [your Database].dbo.sysobjects where ID = ObjectID) Object_Name,
     SUBSTRING(b.text, (a.statement_start_offset/2) + 1,      
     ((CASE statement_end_offset           WHEN -1 THEN DATALENGTH(b.text)          ELSE a.statement_end_offset END
               - a.statement_start_offset)/2) + 1) AS statement_text,               
     total_worker_time as CPU_time, 
     *    
FROM 
     sys.dm_exec_query_stats a  CROSS APPLY 
     sys.dm_exec_sql_text (a.sql_handle) AS b  
WHERE 
     b.dbid = DB_ID('[Your database]') and            -- Filter by Database
     last_execution_time > '2009-10-06 12:52'   -- Filter by Time
ORDER BY 
     a.last_execution_time

srkvellanki
Starting Member

14 Posts

Posted - 2009-10-12 : 09:47:22

Hi,

I substituted "Your database" with my Db Name,it displayed no rows.

Regards
Sri

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-10-14 : 15:54:09

quote:
Originally posted by srkvellanki

Hi,

I substituted "Your database" with my Db Name,it displayed no rows.

Regards
Sri

Not too sure why. I know I tried this on SQL Server Express and I did not get too much results.

Try removing the 'b.dbid = DB_ID('[Your database]') and' line.

Try just: select * from sys.dm_exec_query_stats

Here is one article on the topic:
http://sqlserverpedia.com/wiki/DM_Objects_-_Sys.dm_exec_query_stats

srkvellanki
Starting Member

14 Posts

Posted - 2009-10-15 : 10:17:59

Dennis,Thank you very much.The link you gave is very informative.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources