July 31, 2013 at 2:22 am
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.
July 31, 2013 at 7:31 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2013 at 12:24 pm
Hey, i have updated the post, with the code and data you requested.
July 31, 2013 at 12:39 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2013 at 11:08 pm
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
August 1, 2013 at 1:13 am
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
August 1, 2013 at 8:36 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 4, 2013 at 5:05 am
That query looks overly complex, is it not possible to use IF ELSE instead of the switch case?
August 5, 2013 at 7:18 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 1:43 am
Okay, thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply