CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nausif
Nausif

Reputation: 400

How to make multiple columns based on rows in SQL Server

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.

Upvotes: 1

Views: 4065

Answers (3)

Ravi Singh
Ravi Singh

Reputation: 2080

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)

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

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

Upvotes: 2

Chris DaMour
Chris DaMour

Reputation: 4020

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

Upvotes: 0

Related Questions