How to make multiple columns based on rows in SQL Server

Question

I have a table names Attendance Master

id  ||name ||attn_hours || attn_date
---------------------------------------------
142 || abc || 2.00 || 2013-01-17 00:00:00.000
142 || abc || 3.00 || 2013-01-18 00:00:00.000
143 || pqr || 3.00 || 2013-01-17 00:00:00.000
143 || pqr || 2.00 || 2013-01-18 00:00:00.000

I want distinct names and attendance hours in multiple column with column names as attn_date like following

id  ||name ||2013-01-17||2013-01-18
---------------------------------
142 || abc || 2.00 || 3.00
143 || pqr || 3.00 || 2.00

Please help

Thanks in advance.

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! That way, you don't need any messy <br/> tags, either! — marc_s
– marc_s, Commented Jan 26, 2013 at 8:49

Hamlet Hakobyan · Accepted Answer · 2013-01-26 09:18:58Z

2

Try this:

SELECT
  Id, name, [2013-01-17], [2013-01-18]
FROM Tbl
PIVOT
(
  MAX(attn_hours)
  FOR attn_date in ([2013-01-17], [2013-01-18])
) A

or this:

SELECT
  Id, 
  name,
  MAX(CASE WHEN attn_date = '2013-01-17' THEN attn_hours END) [2013-01-17],
  MAX(CASE WHEN attn_date = '2013-01-18' THEN attn_hours END) [2013-01-18]
FROM Tbl
GROUP BY Id, Name

SQL FIDDLE DEMO

edited Jan 26, 2013 at 9:18

answered Jan 26, 2013 at 9:13

Hamlet Hakobyan

33.5k6 gold badges55 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Soumendra Over a year ago

2nd one, without PIVOT worked for me in Google BigQuery. Thanks a lot.

Chris DaMour · Accepted Answer · 2013-01-26 09:18:30Z

0

you need to pivot your data. In sql server 2008 you can do this by following the guide @ http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

then you can understand why this works: http://sqlfiddle.com/#!3/3f8a3/1/0

edited Jan 26, 2013 at 9:18

answered Jan 26, 2013 at 8:55

Chris DaMour

4,09736 silver badges38 bronze badges

Comments

Ravi Singh · Accepted Answer · 2013-01-28 10:01:08Z

0

Try This:

DECLARE @Dates VARCHAR(8000)
Declare @Month int=1

SELECT @Dates = COALESCE(@Dates + '], [', '') + cast(attn_date as varchar(20)) FROM 
(select distinct attn_date from tbl where month(attn_date)=@Month) as a


Set @Dates='['+@Dates+']'

--SELECT @Dates




Declare @query varchar(max)


Set @query=
'SELECT
Id, name, ' + @Dates + ' 
FROM Tbl
PIVOT
(
  MAX(attn_hours)
  FOR attn_date in (' + @Dates + ' )   
) A'

print @query

exec(@query)

edited Jan 28, 2013 at 10:01

answered Jan 28, 2013 at 9:42

Ravi Singh

2,08013 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to make multiple columns based on rows in SQL Server

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related