May 19, 2007 at 1:26 am
Dear all,
I've two tables: Dep and DepRev
------------------------------------------
CREATE TABLE Dep
(DepOrder VARCHAR (8), DepID VARCHAR (8));
INSERT INTO Dep (DepOrder, DepID)
VALUES ('1', '00001111');
INSERT INTO Dep (DepOrder, DepID)
VALUES ('2', '00002222');
INSERT INTO Dep (DepOrder, DepID)
VALUES ('3', '00003333');
------------------------------------------
Dep:
DepOrder DepID
-------- --------
1 00001111
2 00002222
3 00003333
------------------------------------------
CREATE TABLE DepRev
(DepID VARCHAR (8), DepSector VARCHAR (9), DepRevenue(10));
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00001111','Law', '0100000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00001111','Finance', '0200000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00001111','Counsulti', '0300000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00001111','InfTechno', '0400000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00002222','Law', '0500000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00002222','Finance', '0600000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00002222','Counsulti', '0700000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00002222','InfTechno', '0800000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00003333','Law', '0900000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00003333','Finance', '1000000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00003333','Counsulti', '1100000000');
INSERT INTO Dep (DepID VARCHAR, DepSector, DepRevenue)
VALUES ('00003333','InfTechno', '1200000000');
------------------------------------------
Emp:
DepID DepSector DepRevenue
-------- --------- ----------
00001111 Law 0100000000
00001111 Finance 0200000000
00001111 Counsulti 0300000000
00001111 InfTechno 0400000000
00002222 Law 0500000000
00002222 Finance 0600000000
00002222 Counsulti 0700000000
00002222 InfTechno 0800000000
00003333 Law 0900000000
00003333 Finance 1000000000
00003333 Counsulti 1100000000
00003333 InfTechno 1200000000
------------------------------------------
I'm trying to create a Stored Procedure in order to obtain this:
DepSector DepRevenue_DepID00001111 DepRevenue_DepID00002222 DepRevenue_DepID00003333
--------- ------------------------ ------------------------ ------------------------
Law 0100000000 0500000000 0900000000
Finance 0200000000 0600000000 1000000000
Counsulti 0300000000 0700000000 1100000000
InfTechno 0400000000 0800000000 1200000000
I've heard of pivot table but I've no idea how to use them or how to achieve this result.
Thank you in advance for any help
Marco
May 21, 2007 at 3:21 am
SELECT DepSector, [00001111] as [DepRevenue_00001111],[00002222] as [DepRevenue_00002222], [00003333] as [DepRevenue_00003333] FROM DepRev
PIVOT
( max(DepRevenue) for DepId in ([00001111],[00002222],[00003333]))
AS pvt
order BY DepSector
note that: dynamic column creation is not supported in pivot, so if u have unlimited/huge-number-of DepId u cannot use pivot this way....
May 21, 2007 at 7:12 am
You can do dynamic names in an execute statement though.
set
quoted_Identifier off
declare
@x int
select
@x = 15
declare
@sql varchar(2000);
set
@sql = 'select *
from (select LoginName, sum(DCount) [DCount] , convert(varchar(10),PostTime,101) as PostTime
from dbo.Login_Log_DailySummary
group by LoginName, convert(varchar(10),PostTime,101)
) as B
pivot(
sum(DCount)
for PostTime in('
+
stuff
(
(
select N',' + quotename(convert(varchar(10),PostTime,101)) as [text()]
from (select distinct top (@x) PostTime from Login_Log_DailySummary order by PostTime desc) as D
order by PostTime
for XML Path('')), 1, 1, N'')+
')) as P;'
;
--select @sql
exec
(@sql)
This will actualy execute the following SQL statement, which pivot on the dates, and gives me the LoginName and the dates (up to the last 15 dates) as column names.
select
*
from
(select LoginName, sum(DCount) [DCount] , convert(varchar(10),PostTime,101) as PostTime
from
dbo.Login_Log_DailySummary
group
by LoginName, convert(varchar(10),PostTime,101)
)
as B
pivot(
sum
(DCount)
for
PostTime in([05/13/2007],[05/14/2007],[05/15/2007],[05/16/2007],[05/17/2007],[05/18/2007],[05/19/2007],[05/20/2007])) as P;
Sample column output:
LoginName 05/19/2007 05/20/2007
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply