I got stuck at a place while making attendance application. VB.
GUYS CAN SUGGEST ME QUERY FOR FOLLOWING . NO STORE PROCEDURE PLEASE,
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 TrivediPosted Mar 10, 2014, 6:12 AM
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.