Query with Pivot

Question

Hello. I was told that this was the place to go with my sql syntax query questions :)

I have 3 problems/request/questions:

How do I make it group by name? If I use (max(Type) It works just fine but when I combine the types I break it.
I also need to implement the enddate so that I get a type marked for every day between start and enddate like I have shown in result2
And last how can I easy extend so that I can show 5 or 6 weeks at a time?

when I run my query I get this:

(result1)
name Monday Tuesday Wednesday Thursday Friday
Hans NULL | NULL | NULL | NULL | A1, A2,
Marie A1, A2, A3, | NULL | NULL  | NULL | NULL
Hans NULL | A1, A2, | NULL | NULL | NULL
Marie A1, A2, A3, | NULL | NULL | NULL | NULL
Marie A1, A2, A3, | NULL | NULL | NULL | NULL

But it shoud have been:

(result2)
name Monday Tuesday Wednesday Thursday Friday
Hans NULL | A2 | A2 | A2 | A1, A2,
Marie A1, A2, A3, | NULL | NULL | NULL | NULL

Here is the data i'm using.

declare @t table ( id int, name nvarchar(10),StartDate datetime, EndDate datetime,Type nvarchar(10))
insert into @t values (2,'Marie','11/2/2009','11/2/2009', 'A1')
insert into @t values (3,'Hans','11/3/2009','11/6/2009', 'A2')
insert into @t values (4,'Marie','11/2/2009','11/2/2009', 'A3')
insert into @t values (5,'Marie','11/2/2009','11/2/2009', 'A2')
insert into @t values (6,'Hans','11/6/2009','11/6/2009', 'A1')

SELECT

 name,
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday]

FROM (SELECT name,(SELECT Type+', ' FROM @t t Where t.name = o.name for xml path('') )
Types_Combined, Type, Datename(Weekday, StartDate) Myweekname
FROM @t o ) Src PIVOT (max(Types_Combined) FOR Myweekname
IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])) Pvt

I was helped by some nice people on asp.net. I was told to post my question here as well. I will link the forum post together in the end

Answer 1

If you eliminate the TYPE column from your SRC, then it will work as expected.  Anything in the FROM that is NOT used in the aggregate or the IN part of the pivot will be used in a grouping.

SELECT name, 
       [Monday], 
       [Tuesday], 
       [Wednesday], 
       [Thursday], 
       [Friday] 
FROM (SELECT name,
             Types_Combined=STUFF(SELECT ', '+Type
                             FROM @t t 
                             Where t.name = o.name 
                             for xml path('')),1,2,'')
             MyWeekName=Datename(Weekday, StartDate)
      FROM @t o) Src 
PIVOT (max(Types_Combined) 
       FOR Myweekname
       IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])) Pvt 

Also note that I changed the derivation of your TYPES_COMBINED column so that you don't have a trailing comma.

---

--Brad (My Blog)

Answer 2

Hi Brad. 

So problem 1 is gone
I think I may have solved my secound problem by creating a new table. In a way, cause now I have a new problem.

name	11/2/2009	11/3/2009	11/4/2009	11/5/2009	11/6/2009
Hans	NULL	A2, A1	A2, A1	A2, A1	A2, A1
Marie	A1, A3, A2	NULL	NULL	NULL	NULL

Now it combines all the types on every date.
It should have been:

name	11/2/2009	11/3/2009	11/4/2009	11/5/2009	11/6/2009
Hans	NULL	A2	A2	A2	A2, A1
Marie	A1, A3, A2	NULL	NULL	NULL	NULL

Do I need to create a if statement or ?

The new test date.

Create table #t ( id int, name nvarchar(10),StartDate datetime, EndDate datetime,Type nvarchar(10))
insert into #t values (2,'Marie','11/2/2009','11/2/2009', 'A1')
insert into #t values (3,'Hans','11/3/2009','11/6/2009', 'A2')
insert into #t values (4,'Marie','11/2/2009','11/2/2009', 'A3')
insert into #t values (5,'Marie','11/2/2009','11/2/2009', 'A2')
insert into #t values (6,'Hans','11/6/2009','11/6/2009', 'A1')

create table #bookeddates ( id int, dateId int, Dates datetime, Type nvarchar(10))
insert into #bookeddates values (2,1,'11/2/2009', 'A1')
insert into #bookeddates values (3,2,'11/3/2009', 'A2')
insert into #bookeddates values (3,3,'11/4/2009', 'A2')
insert into #bookeddates values (3,4,'11/5/2009', 'A2')
insert into #bookeddates values (3,5,'11/6/2009', 'A2') 
insert into #bookeddates values (4,7,'11/2/2009', 'A3')
insert into #bookeddates values (5,8,'11/2/2009', 'A2')
insert into #bookeddates values (6,9,'11/6/2009', 'A1') 

SELECT
 name,
[11/2/2009],
[11/3/2009],
[11/4/2009],
[11/5/2009],
[11/6/2009]

FROM (SELECT name,Types_Combined=STUFF((SELECT ', '+ Type FROM #t t Where t.name = o.name for xml path('')),1,2,''),

Myweekname=(SELECT #bookeddates.Dates)
FROM #t o
JOIN #bookeddates
ON o.Id=#bookeddates.Id
) Src PIVOT (max(Types_Combined) FOR Myweekname

IN (
[11/2/2009],
[11/3/2009],
[11/4/2009],
[11/5/2009],
[11/6/2009])) Pvt

DROP table #t
DROP table #bookeddates

Answer 3

That's a little more tricky... Try this:

select Name
       ,[11/2/2009]
       ,[11/3/2009] 
       ,[11/4/2009] 
       ,[11/5/2009] 
       ,[11/6/2009]
from (select Name
            ,MyWeekName=Dates
            ,Types_Combined=stuff((select ', '+b.Type
                                   from #t t
                                   join #bookeddates b on t.ID=b.ID
                                   where Name=x.Name and Dates=x.Dates
                                   for xml path('')),1,2,'')
      from (select distinct t.Name,b.Dates
            from #t t
            join #bookeddates b on t.ID=b.ID) x) Src
pivot (max(Types_Combined) 
       for MyWeekName in
       ([11/2/2009], [11/3/2009], [11/4/2009], [11/5/2009], [11/6/2009])) Pvt

---

--Brad (My Blog)

Answer 4

Thank you so much, been trying all knd of stuff all day :)
But I was closing in on the "join".. at least.
Pretty hard language to learn.

So now I just have to figure out how to make it a bit more dynamic. I dont want to hard code all the date columns for a hole year.

Again Thank you so much Brad

Answer 5

Thank you so much, been trying all knd of stuff all day :)
But I was closing in on the "join".. at least.
Pretty hard language to learn.

So now I just have to figure out how to make it a bit more dynamic. I dont want to hard code all the date columns for a hole year.

Again Thank you so much Brad

As Itzik Ben-Gan said, "SQL is easily learned but not easily mastered"

---

Abdallah, PMP, MCTS

Answer 6

I knew you would get better help here :) The problem is quite complex.

---

Premature optimization is the root of all evil in programming.

Donald Knuth, repeating C. A. R. Hoare

My blog

Answer 7

The only reason that this solution was not trivial was because of the duplications of NAME values in your #T table.  That's why I had to do all the derived table (i.e. FROM (SELECT ...)) stuff.  If your data was a little more normalized, it would have been easier.

---

--Brad (My Blog)

Answer 8

What would be more normalized data, can you give me an example?

Answer 9

A couple of examples...

In #BookedDates, there is a Type column, which just seems to duplicate what is already in your #T table.  I think it could be eliminated, unless it's doing something I'm not aware of.

Most importantly, though, is table #T which has the Name column.  It really should be a foreign key into a table with names in it.

For example:

A new table #Names (or something else) would have NameID, Name.  It would have two records, one for Hans (NameID=1) and one for Marie (NameID=2).

Table #T would have ID, NameID, StartDate, EndDate, Type.  And NameID would be equal to either 1 (Hans) or 2 (Marie). 

But now that I've said all that and thought more about it, I take back what I said... I don't think it would help the query much... it would still require derived tables to get what you want.

---

--Brad (My Blog)

Answer 10

Would this sql query be considered as advanced sql or is this normal?
If you have the time. Would you be kind to comment the sql query you gave me.
It is pretty hard to read. And I'm not sure what it does exactly.

Most of it I can read but there are many places where it just dont make any sense.
Other than it just has to been like this to work.
Like: "join #bookeddates b on t.ID=b.ID) x)"   -> Why does it have to have the x) in the end?

Well anyhow it works. normalized data or not. :)
And passing in the columns as a paramenter was easy. so now I'm on my way to new sql adventures.

Answer 11

I reformatted the code so it may make more sense.  Essentially the "x" is just an alias given to the FROM derived table, and "Src" is another alias and "Pvt" is another alias given to the pivot clause.  Every FROM source (if it's not an actual table) must have an alias name.

select Name
       ,[11/2/2009]
       ,[11/3/2009] 
       ,[11/4/2009] 
       ,[11/5/2009] 
       ,[11/6/2009]
from (

      select Name
            ,MyWeekName=Dates
            ,Types_Combined=stuff((select ', '+b.Type
                                   from #t t
                                   join #bookeddates b on t.ID=b.ID
                                   where Name=x.Name and Dates=x.Dates
                                   for xml path('')),1,2,'')
      from (
      
            select distinct t.Name,b.Dates
            from #t t
            join #bookeddates b on t.ID=b.ID
            
           ) x
            
     ) Src


pivot (max(Types_Combined) 
       for MyWeekName in
       ([11/2/2009], [11/3/2009], [11/4/2009], [11/5/2009], [11/6/2009])) Pvt

---

--Brad (My Blog)

Answer 12

Thank you so much for your help.