PIVOT problems

All of the examples I've seen using PIVOT have the pivoted columns known
(hard coded). I want to PIVOT dynamically... here's my problem.
Here's my example:
Script that captures all data (without group by clause)
select
p.procID,
p.procName,
i.itemName
from
tblProcs p
left outer join tblTailoredItems ti on p.procID = ti.ProcID
left outer join tblItems i on ti.itemID = i.itemID
this will in a sense get all data i need but I want to have all itemNames in
tblItems as columns. I need to use PIVOT but I can't figure out how to do
this dynamically.
Please let me know if you need more information. For each itemName (column)
I want the count of how many times the item is tailored under it (for each
procedure)... which should be trivial.
Thanks
- jvHello Jeffrey,

> All of the examples I've seen using PIVOT have the pivoted columns
> known (hard coded). I want to PIVOT dynamically... here's my problem.
Nope, not going to happen in this version of SQL Server, maybe the next.
You can build the query (the select and the pivot) up dynamically use sp_exe
cutesql
to execute it.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Finally, a Mentor that's a dynamic sql advocate:)
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74152348c810f11c91c920@.news.microsoft.com...
> Hello Jeffrey,
>
> Nope, not going to happen in this version of SQL Server, maybe the next.
> You can build the query (the select and the pivot) up dynamically use
sp_executesql
> to execute it.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hello 05ponyGT,

> Finally, a Mentor that's a dynamic sql advocate:)
Well yes and no. In this case, its the only suitable solution to the problem
.
In general, no, I don't like dynamic SQL very much for all of the well-worn
reasons.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||You haven't checked out RAC?:)
www.rac4sql.net
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74154fa8c8111dd31eb4d0@.news.microsoft.com...
> Hello 05ponyGT,
>
> Well yes and no. In this case, its the only suitable solution to the
problem.
> In general, no, I don't like dynamic SQL very much for all of the
well-worn
> reasons.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||That's kind of a bad answer don't you think? I mean they've had this
functionality in Access a long time ago and your telling me that SQL can't
handle this at all'
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74152348c810f11c91c920@.news.microsoft.com...
> Hello Jeffrey,
>
> Nope, not going to happen in this version of SQL Server, maybe the next.
> You can build the query (the select and the pivot) up dynamically use
> sp_executesql to execute it.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||There are no native T-SQL commands to handle this situation. Access
does it, but also consider the fact all data from Access is manipulated
completely on the client - you won't be killing your server performance
as much if you pull all the data to the client in one fell swoop and
pivot/crosstab it there.
Another solution is to attach a SQL table to an Access front end and
use Access to create your xtab.|||Whatsamatter, can't handle the truth?
I heard someone ask where are the server keywords for a factor analysis.
There's lots of good software out there for xtabs and what not (and I don't
mean Access).
Discover the world beyond MS:)
"Jeffrey A. Voigt" <jvelite@.gmail.com> wrote in message
news:%23tXs2J8QGHA.5552@.TK2MSFTNGP14.phx.gbl...
> That's kind of a bad answer don't you think? I mean they've had this
> functionality in Access a long time ago and your telling me that SQL can't
> handle this at all'
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad74152348c810f11c91c920@.news.microsoft.com...
>|||Hello Jeffrey,

> That's kind of a bad answer don't you think? I mean they've had this
> functionality in Access a long time ago and your telling me that SQL
> can't handle this at all'
Um, SQL can do it, its T-SQL that's the problem. Anyway, yes, I totally agre
e
that its a sucky answer.
That said, SQL Server has had transactions for ages, still don't see that
in Access either. The best way to look at this is "its MS's initial introduc
tion
of PIVOT into T-SQL. It works. It will be improved as T-SQL improves."
Glass half full, in other words.
I don't have the SQL 1999 standard specs in front of me, but does that allow
for a dynamic list in a pivot?
Cheers,
Kent
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||PIVOT is not in ANSI SQL.
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7415e2a8c811e00b0b4300@.news.microsoft.com...
> Hello Jeffrey,
>
> Um, SQL can do it, its T-SQL that's the problem. Anyway, yes, I totally
> agree that its a sucky answer.
> That said, SQL Server has had transactions for ages, still don't see that
> in Access either. The best way to look at this is "its MS's initial
> introduction of PIVOT into T-SQL. It works. It will be improved as T-SQL
> improves."
> Glass half full, in other words.
> I don't have the SQL 1999 standard specs in front of me, but does that
> allow for a dynamic list in a pivot?
> Cheers,
> Kent
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>