Cross tab query/ Pivot. How should i do???

Question

I got stuck at a place while making attendance application. VB.
GUYS CAN SUGGEST ME QUERY FOR FOLLOWING . NO STORE PROCEDURE PLEASE,

I have a table in my sql database.
2 columns , name and dates.

NAMES | DATES

abc 1-1-2014
abc 2-1-2014
cde 2-1-2014
cde 3-1-2014
efg 3-1-2014
hij 4-1-2014

i want a query so that i can show this information in GRID.

NAMES 1-1-2014 || 2-1-2014 || 3-1-2014 || 4-1-2014

abc yes yes no no
cde no yes yes no
efg no no yes yes
hij no no no yes

Can u guys suggest me a query for this. I'll do the by myself.
i am not much good at database. Thank u .

Jignesh Trivedi · Answer

hi try.... Create table #temp ( Name varchar(20) AttDate Varchar(10) ) insert into #temp values('abc' '1-1-2014') ('abc' '2-1-2014') ('cde' '2-1-2014') ('cde' '3-1-2014') ('efg' '3-1-2014') ('hij' '4-1-2014') select Name case when [1-1-2014]=0 then 'No' else 'Yes' end as [1-1-2014] case when [2-1-2014]=0 then 'No' else 'Yes' end as [2-1-2014] case when [3-1-2014]=0 then 'No' else 'Yes' end as [3-1-2014] case when [4-1-2014]=0 then 'No' else 'Yes' end as [4-1-2014] from #temp pivot (count (attDate) for AttDate in ([1-1-2014][2-1-2014][3-1-2014][4-1-2014])) as countData here one limitation of query is you have to pass hard code date.... you may remove this using dynamic query... hope this will help you.