Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Timetable query, using CASE Expand / Collapse
Author
Message
Posted Wednesday, July 31, 2013 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 4:18 AM
Points: 6, Visits: 20
Hello

I know this is a total newb question, but i have the following SQL:
SELECT FagNavn, adgangskrav, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate, CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,
Case When Ugedag = 'Mandag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutMandag,
Case When Ugedag = 'Tirsdag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutTirsdag,
Case When Ugedag = 'Onsdag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutOnsdag,
Case When Ugedag = 'Torsdag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutTorsdag,
Case When Ugedag = 'Fredag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutFredag FROM [VisWebHoldSkema] ORDER BY afdeling

This is giving me records for each day, because of the End statement in the Switch Case, but i would like to have all the days in the same record.

Example:

+----------+-----------------+-----------------+----------------+-----------------+----------------+
| holdId | startSlutMandag | startSlutTirsdag | startSlutOnsdag | startSlutTorsdag | startSlutFredag |
+----------+-----------------+----------------+-----------------+----------------+
| 58f42ab | 09:00 - 15:30 | | | | |
+----------+-----------------+----------------+-----------------+----------------+
| 58f42ab | | | 08:00 - 15:00 | | |
+----------+-----------------+----------------+-----------------+----------------+
| 58f42ab | | | | 08:00 - 15:00 | |
+----------+-----------------+----------------+-----------------+----------------+
| 58f42ab | | | | | 08:00 - 14:30 |
+----------+-----------------+----------------+-----------------+----------------+

But this is what i want:
+----------+-----------------+-----------------+----------------+-----------------+----------------+
| holdId | startSlutMandag | startSlutTirsdag | startSlutOnsdag | startSlutTorsdag | startSlutFredag |
+----------+-----------------+-----------------+----------------+-----------------+----------------+
| 58f42ab | 09:00 - 15:30 | | 08:00 - 15:00 | 08:00 - 15:00 | 08:00 - 14:30 |
+----------+-----------------+----------------+-----------------+----------------+

DDL and INSERT's
CREATE TABLE example (
UserId nvarchar(200),
Ugedag nvarchar(200),
Frakl nvarchar(200),
Tilkl nvarchar(200)
)

INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Mandag', '08:00', '16:30')
INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Tirsdag', '10:00', '15:00')
INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Onsdag', '09:00', '16:00')
INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Torsdag', '08:00', '14:00')
INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Fredag', '08:00', '14:00')

Any help is greately appriciated.
Post #1479341
Posted Wednesday, July 31, 2013 7:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 13,326, Visits: 12,813
Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1479479
Posted Wednesday, July 31, 2013 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 4:18 AM
Points: 6, Visits: 20
Hey, i have updated the post, with the code and data you requested.
Post #1479639
Posted Wednesday, July 31, 2013 12:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 13,326, Visits: 12,813
Excellent job posting the details. I just couldn't quite see what you were trying to do before.

Something like this should get what you want.

select UserId,
MAX(Case when Ugedag = 'Mandag' then Frakl + ' - ' + Tilkl else null end) as startSlutMandag,
MAX(Case when Ugedag = 'Tirsdag' then Frakl + ' - ' + Tilkl else null end) as startSlutTirsdag,
MAX(Case when Ugedag = 'Onsdag' then Frakl + ' - ' + Tilkl else null end) as startSlutOnsdag,
MAX(Case when Ugedag = 'Torsdag' then Frakl + ' - ' + Tilkl else null end) as startSlutTorsdag,
MAX(Case when Ugedag = 'Fredag' then Frakl + ' - ' + Tilkl else null end) as startSlutFredag
from example
group by UserId



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1479646
Posted Wednesday, July 31, 2013 11:08 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 9:37 PM
Points: 1,454, Visits: 136
Use Group clause

Here is the code, this might helps you

CREATE TABLE example (
holdId nvarchar(200),
Ugedag nvarchar(200),
Frakl nvarchar(200),
Tilkl nvarchar(200)
)

INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Mandag', '08:00', '16:30')
INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Tirsdag', '10:00', '15:00')
INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Onsdag', '09:00', '16:00')
INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Torsdag', '08:00', '14:00')
INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Fredag', '08:00', '14:00')



SELECT holdId,--FagNavn, adgangskrav, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate, CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,
max(Case When Ugedag = 'Mandag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutMandag,
max(Case When Ugedag = 'Tirsdag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutTirsdag,
max(Case When Ugedag = 'Onsdag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutOnsdag,
max(Case When Ugedag = 'Torsdag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutTorsdag,
max(Case When Ugedag = 'Fredag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutFredag FROM example
group by holdId
Post #1479785
Posted Thursday, August 1, 2013 1:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 4:18 AM
Points: 6, Visits: 20
Thanks for the quick reply.

Im getting the following error when using the query:
Msg 8120, Level 16, State 1, Line 1
Column 'VisWebHoldSkema.FagNavn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Query:
SELECT FagNavn, adgangskrav, frakl, tilkl, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate, CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,

MAX(Case when Ugedag = 'Mandag' then frakl + ' - ' + tilkl else null end) as startSlutMandag,
MAX(Case when Ugedag = 'Tirsdag' then frakl + ' - ' + tilkl else null end) as startSlutTirsdag,
MAX(Case when Ugedag = 'Onsdag' then frakl + ' - ' + tilkl else null end) as startSlutOnsdag,
MAX(Case when Ugedag = 'Torsdag' then frakl + ' - ' + tilkl else null end) as startSlutTorsdag,
MAX(Case when Ugedag = 'Fredag' then frakl + ' - ' + tilkl else null end) as startSlutFredag

FROM [VisWebHoldSkema] ORDER BY afdeling

Post #1479810
Posted Thursday, August 1, 2013 8:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 13,326, Visits: 12,813
madsovenielsen (8/1/2013)
Thanks for the quick reply.

Im getting the following error when using the query:
Msg 8120, Level 16, State 1, Line 1
Column 'VisWebHoldSkema.FagNavn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Query:
SELECT FagNavn, adgangskrav, frakl, tilkl, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate, CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,

MAX(Case when Ugedag = 'Mandag' then frakl + ' - ' + tilkl else null end) as startSlutMandag,
MAX(Case when Ugedag = 'Tirsdag' then frakl + ' - ' + tilkl else null end) as startSlutTirsdag,
MAX(Case when Ugedag = 'Onsdag' then frakl + ' - ' + tilkl else null end) as startSlutOnsdag,
MAX(Case when Ugedag = 'Torsdag' then frakl + ' - ' + tilkl else null end) as startSlutTorsdag,
MAX(Case when Ugedag = 'Fredag' then frakl + ' - ' + tilkl else null end) as startSlutFredag

FROM [VisWebHoldSkema] ORDER BY afdeling



This is because you don't have a group by but you do have aggregate data. It is a bit difficult to say what you need to do here but as a guess you might try putting the aggregate columns and the primary key into a cte then you can join to the cte from your query. Something like this maybe?

with AggregateData as
(
select UserID,
MAX(Case when Ugedag = 'Mandag' then frakl + ' - ' + tilkl else null end) as startSlutMandag,
MAX(Case when Ugedag = 'Tirsdag' then frakl + ' - ' + tilkl else null end) as startSlutTirsdag,
MAX(Case when Ugedag = 'Onsdag' then frakl + ' - ' + tilkl else null end) as startSlutOnsdag,
MAX(Case when Ugedag = 'Torsdag' then frakl + ' - ' + tilkl else null end) as startSlutTorsdag,
MAX(Case when Ugedag = 'Fredag' then frakl + ' - ' + tilkl else null end) as startSlutFredag
from example
group by UserId
)

SELECT FagNavn, adgangskrav, frakl, tilkl, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate,
CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,
ad.startSlutMandag, ad.startSlutTirsdag, ad.startSlutOnsdag, ad.startSlutTorsdag, ad.startSlutFredag
from VisWebHoldSkema vwhs
join AggregateData ad on ad.UserId = vwhs.FagNavn --or whatever you would use to join here
ORDER BY afdeling



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1479973
Posted Sunday, August 4, 2013 5:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 4:18 AM
Points: 6, Visits: 20
That query looks overly complex, is it not possible to use IF ELSE instead of the switch case?
Post #1480764
Posted Monday, August 5, 2013 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 13,326, Visits: 12,813
madsovenielsen (8/4/2013)
That query looks overly complex, is it not possible to use IF ELSE instead of the switch case?


It really isn't that complex. You could instead choose to not use the cte but then you will need to group by all of the non-aggregate columns.

You can't use IF ELSE for data. The IF ELSE construct is used to control the flow of statements, that is why we use the case expression here.

What this query is doing is known as a cross tab. You can read more about the techniques that you could use by following the links in my signature.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480904
Posted Tuesday, August 6, 2013 1:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 4:18 AM
Points: 6, Visits: 20
Okay, thanks.
Post #1481196
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse