Problem with PIVOT query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with PIVOT query

Author

Topic

hbkelkar
Starting Member

2 Posts

Posted - 2011-10-26 : 15:51:01

Hello Everyone ,

I am just been introduced to PIVOT and done one or two very simple hard coded fields list pivot queries.

Now i am really struggling with a pivot query (working on a SQL 2005 Express database),
The Source query works fine and when i tried to convert into pivot its just not working. totally confused now :(

The actual query is,

SELECT al.vocNo, al.prInvNo, al.achead,
al.purhcode, a2.hname AS PartyName, a.hname AS Account, SUM(dr) AS drtot
FROM acLedger al INNER JOIN acheads a
ON a.id = al.achead INNER JOIN acheads a2
ON a2.id = al.purhcode
WHERE al.vocType = 'PURC' AND vocDate BETWEEN '20110401' AND '20111030' AND AL.compid = 1
AND al.achead IN (SELECT vs.prac FROM VatSlab vs UNION SELECT vs2.prvatac
FROM VatSlab vs2 )
GROUP BY al.vocNo, al.prInvNo, al.achead,al.purhcode, a2.hname, a.hname

The result of this query is ..

1268 1071 27 60 aaa pharma Purchase 5% 1687.00
1268 1071 28 60 aaa pharma Input Vat 5% 84.35
1270 418 27 61 bbb pharma Purchase 5% 6500.00
1270 418 28 61 bbb pharma Input Vat 5% 325.00
1272 417 27 62 bbb pharma Purchase 5% 11600.00
1272 417 28 62 abc pharma Input Vat 5% 580.00
1275 13121 27 63 ccc pharma Purchase 5% 6643.34
1275 13121 28 63 ccc pharma Input Vat 5% 332.18
1276 187 27 63 ccc pharma Purchase 4% 17817.81
1276 187 28 63 ccc pharma Input Vat 4% 890.95

What i am trying to achive is some thing like..

Vocno invno achead pname purchase 5% inputvat 5% purchase 4% input vat 4%
1268 1071 60 aaa pharma 1687.00 84.35 0 0
1270 418 61 bbb pharma 6400.00 325.00 0 0
1276 187 63 ccc pharma 0 0 17817.81 890.95

I have also tried to get the pivit columns DYNAMICALLY from VAT table

which is having a structure

vatpc / vatac

Which is linked to accont table with account.id = vat.vatac.

ok, my so far, non working pivot query is ..

DECLARE @colNames VARCHAR(8000)

; WITH PurAcCTE (pract,acName)
AS ( SELECT prac, hName FROM
(SELECT prac
FROM VatSlab vs
UNION
SELECT prvatac
FROM VatSlab vs2) o INNER JOIN acheads a ON a.id = o.prac)

SELECT @ColNames =
STUFF(
(
SELECT ', [' + REPLACE(REPLACE(REPLACE(p.acName, '/', '_'), ',', '_'), '-', '_') + ']'
FROM vatslab vs INNER JOIN purAcCTE p
ON vs.prac = p.pract
FOR XML PATH('')
)
, 1
, 2
, '')

; WITH inCTE (prinvno, vocno, vocdate, purhcode, partyName, achead, account, drtot)
AS (
SELECT al.vocNo, CONVERT(VARCHAR(10),al.vocDate,105) AS VocDate, al.prInvNo, al.achead,
al.purhcode, a2.hname AS PartyName, a.hname AS Account, SUM(dr) AS drtot
FROM acLedger al INNER JOIN acheads a
ON a.id = al.achead INNER JOIN acheads a2
ON a2.id = al.purhcode
WHERE al.vocType = 'PURC' AND vocDate BETWEEN '20110401' AND '20111030' AND AL.compid = 1
AND al.achead IN (SELECT vs.prac FROM VatSlab vs UNION SELECT vs2.prvatac
FROM VatSlab vs2 )
GROUP BY al.vocNo, al.vocdate, al.prInvNo, al.achead,al.purhcode, a2.hname, a.hname
)

SELECT vocno, vocdate, prinvno, partyName, @colNames
FROM (SELECT vocno, vocdate, prinvno, PartyName, account, drtot
FROM inCTE INNER JOIN purAcCTE pc ON pc.pract = inCTE.achead
GROUP BY vocno, vocdate, prinvno, partyname, account, drtot ) InSrc
PIVOT (
AVG(drtot) FOR account IN ( @colNames)) AS pvt

The error is in the last line of the code block that is..

Incorrect syntax near '@colNames'.

Even after fighting with the code for hours , its not still working..!! :(

Can anyone help me in pointing the error so that i can make the PIVOT to work ?

Thanks in advance.

Regards,
Hemanth

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 15:57:10

maybe if you post the DDL of the tables and sample data in DML form

EDIT: And what the expect results should be
Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

hbkelkar
Starting Member

2 Posts

Posted - 2011-10-26 : 16:37:29

quote:
Originally posted by X002548

maybe if you post the DDL of the tables and sample data in DML form

EDIT: And what the expect results should be
Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Thank you for quick reply..
Here are the DDL and DML scripts for the sample data which i have posted in my above query..
CREATE TABLE [dbo].[acLedger](
[id] [int] IDENTITY(1,1) NOT NULL,
[vocNo] [varchar](15) ,
[vocDate] [datetime] NOT NULL,
[achead] [int] NOT NULL,
[acGrp] [int] NOT NULL,
[vocType] [varchar](5) ,
[dr] [decimal](12, 2) NOT NULL ,
[cr] [decimal](12, 2) NOT NULL ,
[prInvNo] [varchar](50) ,
[purhcode] [int] NULL
) ON [PRIMARY]

INSERT INTO acLedger (vocno, vocdate, achead, acgrp, voctype, dr, cr, prInvno, purhcode)
VALUES (1268, '201104008', 27, 5, 'PURC', 1687.00, 0, '1071', 60)
INSERT INTO acLedger (vocno, vocdate, achead, acgrp, voctype, dr, cr, prInvno, purhcode)
VALUES (1268, '201104008', 28, 5, 'PURC', 84.35, 0, '1071', 60)
INSERT INTO acLedger (vocno, vocdate, achead, acgrp, voctype, dr, cr, prInvno, purhcode)
VALUES (1270, '201104008', 27, 5, 'PURC', 6500.00, 0, '418', 61)
INSERT INTO acLedger (vocno, vocdate, achead, acgrp, voctype, dr, cr, prInvno, purhcode)
VALUES (1268, '201104008', 27, 5, 'PURC', 325.00, 0, '418', 61)

CREATE TABLE acHeads (
id INT NOT NULL ,
hname VARCHAR(254) NOT NULL)

INSERT INTO acheads (id, hname)
VALUES (60, 'aaa pharma')
INSERT INTO acheads (id, hname)
VALUES (61, 'bbb pharma')
INSERT INTO acheads (id, hname)
VALUES (27, 'purchase 5%')
INSERT INTO acheads (id, hname)
VALUES (28, 'vat 5%')
INSERT INTO acheads (id, hname)
VALUES (29, 'purchase 4%')
INSERT INTO acheads (id, hname)
VALUES (30, 'vat 4%')

CREATE TABLE vatslab(
id INT IDENTITY NOT NULL ,
vatpc DECIMAL(12,2) NOT NULL ,
pract INT NOT NULL ,
vatact INT NOT NULL )

INSERT INTO vatslab (vatpc, pract, vatact)
VALUES (5.0, 27,28)
INSERT INTO vatslab (vatpc, pract, vatact)
VALUES (4.0, 29, 30)

And the expected result, what im trying to achieve is like..
What i am trying to achive is some thing like..

Vocno invno achead pname purchase 5% inputvat 5% purchase 4% input vat 4%
1268 1071 60 aaa pharma 1687.00 84.35 0 0
1270 418 61 bbb pharma 6400.00 325.00 0 0
1276 187 63 ccc pharma 0 0 17817.81 890.95

[Edit] : This pivot works if i " hardcode " the pivot fields i.e, purchase 5% inputvat 5% purchase 4% input vat 4%, which i dont' want to do..
so please advice to repair the qiery..

thanks in advance.

Regards,
Hemanth

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources