Prev: how to delete/archive huge data from a table without much issues!
Next: Reporting Services And Object Data Source
From: tshad on 17 Jun 2010 21:18
Can you do a pivot in a join where you pass a value into the pivot table as
part of the query?

In the following example, I am trying to do my pivot select using a value
from the first table (TABLE1), but when I use Value1 in my pivot table, I
get an error Invalid Column.

I also tried Table1.Value1 but got a can't be bound.

SELECT Value1, Value2
FROM TABLE1
INNER JOIN
(
SELECT Value1,[2],[3],[4]
FROM
(
SELECT Category, RatingValue from RatingTable
WHERE RatingID = Value1 <---- Problem with Value1
) AS SourceTable
PIVOT
(
SUM(RatingValue)
For Category in ([2],[3],[4])
) AS PivotTable
) AS Rating ON Rating.Value1 = Table1.Value1


Is there a way to do this?

I also tried to make this a derived table and put the join on that table but
got the same results.

Thanks,

Tom


From: tshad on 17 Jun 2010 22:36
I figured out.

I just took out the where clause and moved it to the on condition of the
Join. Then it worked fine.

Thanks,

Tom

"tshad" <tfs(a)dslextreme.com> wrote in message
news:uTSrlQoDLHA.4308(a)TK2MSFTNGP04.phx.gbl...
> Can you do a pivot in a join where you pass a value into the pivot table
> as part of the query?
>
> In the following example, I am trying to do my pivot select using a value
> from the first table (TABLE1), but when I use Value1 in my pivot table, I
> get an error Invalid Column.
>
> I also tried Table1.Value1 but got a can't be bound.
>
> SELECT Value1, Value2
> FROM TABLE1
> INNER JOIN
> (
> SELECT Value1,[2],[3],[4]
> FROM
> (
> SELECT Category, RatingValue from RatingTable
> WHERE RatingID = Value1 <---- Problem with Value1
> ) AS SourceTable
> PIVOT
> (
> SUM(RatingValue)
> For Category in ([2],[3],[4])
> ) AS PivotTable
> ) AS Rating ON Rating.Value1 = Table1.Value1
>
>
> Is there a way to do this?
>
> I also tried to make this a derived table and put the join on that table
> but got the same results.
>
> Thanks,
>
> Tom
>


 | 
Pages: 1
Prev: how to delete/archive huge data from a table without much issues!
Next: Reporting Services And Object Data Source