Hi there!
I've played a little bit with the PIVOT operator in SQL Server 2005 and have
a question:
Can I use a Sub-Select for the values in the IN clause? Because I can't
hardcode these values because they are dynamic (based on user input)...
Thanks
Klaus Aschenbrenner
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerKlaus
DECLARE @.st VARCHAR(50)
SET @.st='5,6'
EXEC('
SELECT *
FROM ItemAttributes AS ATR
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemid IN('+@.st+')')
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:ea4lgqnRGHA.6084@.TK2MSFTNGP14.phx.gbl...
> Hi there!
> I've played a little bit with the PIVOT operator in SQL Server 2005 and
> have a question:
> Can I use a Sub-Select for the values in the IN clause? Because I can't
> hardcode these values because they are dynamic (based on user input)...
> Thanks
> Klaus Aschenbrenner
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||> Can I use a Sub-Select for the values in the IN clause? Because I
> can't hardcode these values because they are dynamic (based on user
> input)...
Not in SQL Server 2005. Maybe next version...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
Showing posts with label operator. Show all posts
Showing posts with label operator. Show all posts
Wednesday, March 7, 2012
Monday, February 20, 2012
PIVOT operator for variable number of transformations
Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:
SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt
this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.
I'm trying this query:
SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt
this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.
PIVOT for non-aggregate data?
I've used UNPIVOT for cleaning up data from tables that violate 1NF.
However, the PIVOT operator seems to behave a little differently, and I
guess I may have misunderstood it from using UNPIVOT (I know they're
not exact complements, in spite of their names). Could someone tell me
if I'm right on this? It seems that PIVOT is only useful in aggregates.
In other words, with the following data (schema can be inferred to
point of relevance):
-- Assume that it's known each person will have a maximum of three
cars. This isn't my exact problem, but can illustrate the conceptual
hurdle.
PersonName AutomobileNumber MakeModel
---
Joe 1 Peugeot505
Joe 2 HondaAccord
Joe 3 LincolnMarkIII
Bob 1 HondaAccord
Bob 2 DMCCoupe
and I want this result (in SQL 2000, I would build this w/ temp tables
and/or joins)
PersonName Automobile1 Automobile2 Automobile3
----
--
Joe Peugeot505 HondaAccord
LincolnMarkIII
Bob HondaAccord DMCCoupe NULL
I assume PIVOT isn't really supposed to be used like this, is it? It's
only for aggregates. Is that right? It'd be nice if the following
worked (and it may be syntactically incorrect as it sits, so forgive
me):
SELECT PersonName, [1] AS Automobile1, [2] AS Automobile2, [3] A
S
Automobile3
FROM PeoplesCars PIVOT (MakeModel FOR AutomobileNumber IN ([1], [2],
[3])) x
-AlanMy apologies to posting to sqlserver.server -- it's late. I was
thinking I was in sqlserver.programming
-Alan|||Alan
CREATE TABLE Foo
(
PersonName NVARCHAR(30) NOT NULL,
AutomobileNumber INT NOT NULL,
MakeModel NVARCHAR(30) NOT NULL
)
INSERT INTO Foo VALUES( N'Joe',1, 'Peugeot505')
INSERT INTO Foo VALUES( N'Joe',2, 'HondaAccord')
INSERT INTO Foo VALUES( N'Joe',3, 'LincolnMarkIII')
INSERT INTO Foo VALUES( N'Bob',1, 'HondaAccord')
INSERT INTO Foo VALUES( N'Bob',2, 'DMCCoupe')
SELECT PersonName,
MAX(CASE WHEN AutomobileNumber =1 THEN MakeModel END ) Automobile1,
MAX(CASE WHEN AutomobileNumber =2 THEN MakeModel END ) Automobile2,
MAX(CASE WHEN AutomobileNumber =3 THEN MakeModel END) Automobile3
FROM foo
GROUP BY PersonName
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1147588956.476925.259410@.i39g2000cwa.googlegroups.com...
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
>|||Alan Samet wrote:
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
This works:
SELECT PersonName,
[1] AS Automobile1, [2] AS Automobile2, [3] AS Automobile3
FROM PeoplesCars
PIVOT (MIN(MakeModel) FOR AutomobileNumber IN ([1], [2], [3])) x
;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
However, the PIVOT operator seems to behave a little differently, and I
guess I may have misunderstood it from using UNPIVOT (I know they're
not exact complements, in spite of their names). Could someone tell me
if I'm right on this? It seems that PIVOT is only useful in aggregates.
In other words, with the following data (schema can be inferred to
point of relevance):
-- Assume that it's known each person will have a maximum of three
cars. This isn't my exact problem, but can illustrate the conceptual
hurdle.
PersonName AutomobileNumber MakeModel
---
Joe 1 Peugeot505
Joe 2 HondaAccord
Joe 3 LincolnMarkIII
Bob 1 HondaAccord
Bob 2 DMCCoupe
and I want this result (in SQL 2000, I would build this w/ temp tables
and/or joins)
PersonName Automobile1 Automobile2 Automobile3
----
--
Joe Peugeot505 HondaAccord
LincolnMarkIII
Bob HondaAccord DMCCoupe NULL
I assume PIVOT isn't really supposed to be used like this, is it? It's
only for aggregates. Is that right? It'd be nice if the following
worked (and it may be syntactically incorrect as it sits, so forgive
me):
SELECT PersonName, [1] AS Automobile1, [2] AS Automobile2, [3] A
S
Automobile3
FROM PeoplesCars PIVOT (MakeModel FOR AutomobileNumber IN ([1], [2],
[3])) x
-AlanMy apologies to posting to sqlserver.server -- it's late. I was
thinking I was in sqlserver.programming
-Alan|||Alan
CREATE TABLE Foo
(
PersonName NVARCHAR(30) NOT NULL,
AutomobileNumber INT NOT NULL,
MakeModel NVARCHAR(30) NOT NULL
)
INSERT INTO Foo VALUES( N'Joe',1, 'Peugeot505')
INSERT INTO Foo VALUES( N'Joe',2, 'HondaAccord')
INSERT INTO Foo VALUES( N'Joe',3, 'LincolnMarkIII')
INSERT INTO Foo VALUES( N'Bob',1, 'HondaAccord')
INSERT INTO Foo VALUES( N'Bob',2, 'DMCCoupe')
SELECT PersonName,
MAX(CASE WHEN AutomobileNumber =1 THEN MakeModel END ) Automobile1,
MAX(CASE WHEN AutomobileNumber =2 THEN MakeModel END ) Automobile2,
MAX(CASE WHEN AutomobileNumber =3 THEN MakeModel END) Automobile3
FROM foo
GROUP BY PersonName
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1147588956.476925.259410@.i39g2000cwa.googlegroups.com...
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
>|||Alan Samet wrote:
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
This works:
SELECT PersonName,
[1] AS Automobile1, [2] AS Automobile2, [3] AS Automobile3
FROM PeoplesCars
PIVOT (MIN(MakeModel) FOR AutomobileNumber IN ([1], [2], [3])) x
;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
PIVOT for non-aggregate data?
I've used UNPIVOT for cleaning up data from tables that violate 1NF.
However, the PIVOT operator seems to behave a little differently, and I
guess I may have misunderstood it from using UNPIVOT (I know they're
not exact complements, in spite of their names). Could someone tell me
if I'm right on this? It seems that PIVOT is only useful in aggregates.
In other words, with the following data (schema can be inferred to
point of relevance):
-- Assume that it's known each person will have a maximum of three
cars. This isn't my exact problem, but can illustrate the conceptual
hurdle.
PersonName AutomobileNumber MakeModel
---
Joe 1 Peugeot505
Joe 2 HondaAccord
Joe 3 LincolnMarkIII
Bob 1 HondaAccord
Bob 2 DMCCoupe
and I want this result (in SQL 2000, I would build this w/ temp tables
and/or joins)
PersonName Automobile1 Automobile2 Automobile3
-----
Joe Peugeot505 HondaAccord
LincolnMarkIII
Bob HondaAccord DMCCoupe NULL
I assume PIVOT isn't really supposed to be used like this, is it? It's
only for aggregates. Is that right? It'd be nice if the following
worked (and it may be syntactically incorrect as it sits, so forgive
me):
SELECT PersonName, [1] AS Automobile1, [2] AS Automobile2, [3] AS
Automobile3
FROM PeoplesCars PIVOT (MakeModel FOR AutomobileNumber IN ([1], [2],
[3])) x
-AlanMy apologies to posting to sqlserver.server -- it's late. I was
thinking I was in sqlserver.programming
-Alan|||Alan
CREATE TABLE Foo
(
PersonName NVARCHAR(30) NOT NULL,
AutomobileNumber INT NOT NULL,
MakeModel NVARCHAR(30) NOT NULL
)
INSERT INTO Foo VALUES( N'Joe',1, 'Peugeot505')
INSERT INTO Foo VALUES( N'Joe',2, 'HondaAccord')
INSERT INTO Foo VALUES( N'Joe',3, 'LincolnMarkIII')
INSERT INTO Foo VALUES( N'Bob',1, 'HondaAccord')
INSERT INTO Foo VALUES( N'Bob',2, 'DMCCoupe')
SELECT PersonName,
MAX(CASE WHEN AutomobileNumber =1 THEN MakeModel END ) Automobile1,
MAX(CASE WHEN AutomobileNumber =2 THEN MakeModel END ) Automobile2,
MAX(CASE WHEN AutomobileNumber =3 THEN MakeModel END) Automobile3
FROM foo
GROUP BY PersonName
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1147588956.476925.259410@.i39g2000cwa.googlegroups.com...
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
>|||Alan Samet wrote:
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
This works:
SELECT PersonName,
[1] AS Automobile1, [2] AS Automobile2, [3] AS Automobile3
FROM PeoplesCars
PIVOT (MIN(MakeModel) FOR AutomobileNumber IN ([1], [2], [3])) x ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
However, the PIVOT operator seems to behave a little differently, and I
guess I may have misunderstood it from using UNPIVOT (I know they're
not exact complements, in spite of their names). Could someone tell me
if I'm right on this? It seems that PIVOT is only useful in aggregates.
In other words, with the following data (schema can be inferred to
point of relevance):
-- Assume that it's known each person will have a maximum of three
cars. This isn't my exact problem, but can illustrate the conceptual
hurdle.
PersonName AutomobileNumber MakeModel
---
Joe 1 Peugeot505
Joe 2 HondaAccord
Joe 3 LincolnMarkIII
Bob 1 HondaAccord
Bob 2 DMCCoupe
and I want this result (in SQL 2000, I would build this w/ temp tables
and/or joins)
PersonName Automobile1 Automobile2 Automobile3
-----
Joe Peugeot505 HondaAccord
LincolnMarkIII
Bob HondaAccord DMCCoupe NULL
I assume PIVOT isn't really supposed to be used like this, is it? It's
only for aggregates. Is that right? It'd be nice if the following
worked (and it may be syntactically incorrect as it sits, so forgive
me):
SELECT PersonName, [1] AS Automobile1, [2] AS Automobile2, [3] AS
Automobile3
FROM PeoplesCars PIVOT (MakeModel FOR AutomobileNumber IN ([1], [2],
[3])) x
-AlanMy apologies to posting to sqlserver.server -- it's late. I was
thinking I was in sqlserver.programming
-Alan|||Alan
CREATE TABLE Foo
(
PersonName NVARCHAR(30) NOT NULL,
AutomobileNumber INT NOT NULL,
MakeModel NVARCHAR(30) NOT NULL
)
INSERT INTO Foo VALUES( N'Joe',1, 'Peugeot505')
INSERT INTO Foo VALUES( N'Joe',2, 'HondaAccord')
INSERT INTO Foo VALUES( N'Joe',3, 'LincolnMarkIII')
INSERT INTO Foo VALUES( N'Bob',1, 'HondaAccord')
INSERT INTO Foo VALUES( N'Bob',2, 'DMCCoupe')
SELECT PersonName,
MAX(CASE WHEN AutomobileNumber =1 THEN MakeModel END ) Automobile1,
MAX(CASE WHEN AutomobileNumber =2 THEN MakeModel END ) Automobile2,
MAX(CASE WHEN AutomobileNumber =3 THEN MakeModel END) Automobile3
FROM foo
GROUP BY PersonName
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1147588956.476925.259410@.i39g2000cwa.googlegroups.com...
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
>|||Alan Samet wrote:
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
This works:
SELECT PersonName,
[1] AS Automobile1, [2] AS Automobile2, [3] AS Automobile3
FROM PeoplesCars
PIVOT (MIN(MakeModel) FOR AutomobileNumber IN ([1], [2], [3])) x ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Subscribe to:
Posts (Atom)