I need Build Query Following Outputs [Resolved]

Posted by Jayakumars under ASP.NET AJAX on 2/21/2015 | Points: 10 | Views : 394 | Status : [Member] [MVP] | Replies : 4
hi
ID Day DayNo
1 SUN 1
2 MON 2
3 TUE 3
4 WED 4
5 THU 5
6 FRI 6
7 SAT 7

I need Pivot like this
1 SUN MON TUE WED THU FRI SAT
2 False False False False False False False
3 False False False False False False False
4 False False False False False False False

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 2/24/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
--Sample Data
CREATE TABLE PivotTable ( ID int, DayNme char(3), DayNo int)
INSERT PivotTable
SELECT 1 , 'SUN', 1 union all
SELECT 2 , 'MON', 2union all
SELECT 3 , 'TUE', 3union all
SELECT 4 , 'WED', 4union all
SELECT 5 , 'THU', 5union all
SELECT 6, 'FRI', 6union all
SELECT 7 , 'SAT', 7

-- Pivot Query
SELECT ID, CASE WHEN [SUN] >= 1 THEN 'True' ELSE 'False' END AS [SUN]
, CASE WHEN [MON] >= 1 THEN 'True' ELSE 'False' END AS [MON]
, CASE WHEN [TUE] >= 1 THEN 'True' ELSE 'False' END AS [TUE]
, CASE WHEN [WED] >= 1 THEN 'True' ELSE 'False' END AS [WED]
, CASE WHEN [THU] >= 1 THEN 'True' ELSE 'False' END AS [THU]
, CASE WHEN [FRI] >= 1 THEN 'True' ELSE 'False' END AS [FRI]
, CASE WHEN [SAT] >= 1 THEN 'True' ELSE 'False' END AS [SAT]
FROM PivotTable P
PIVOT ( COUNT(DayNo) FOR DayNme IN ([SUN], [MON], [TUE], [WED], [THU], [FRI],[SAT]))P


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 2/24/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Bandi Good Post

I have one Clarification I need column generated depends on row

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 2/24/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
bandi
can u provide ur email id plz i have lot of doubts in sql server

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/24/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
explain your query with sample data....

I doesn't have access to GMAIL in my office

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response