December 30, 2015 at 6:10 pm
Hello,
I've Table Design and storing data in vertical format as follow
CREATE TABLE [dbo].[crpt_ImbanganDuga_4_Vertical](
[batch_Id] [uniqueidentifier] NULL,
[group_1Digit] [char](5) NULL,
[group_2Digit] [char](5) NULL,
[group_3Digit] [char](5) NULL,
[chart_code] [char](5) NULL,
[desc_text] [nvarchar](300) NULL,
[close_amt] [decimal](18, 2) NULL,
[kod] [nvarchar](10) NULL,
[year_num] [int] NULL
) ON [PRIMARY];
/****** Object: Index [crpt_ImbanganDuga_4_Vertical_UQ1] Script Date: 31/12/2015 8:49:39 AM ******/
ALTER TABLE [dbo].[crpt_ImbanganDuga_4_Vertical] ADD CONSTRAINT [crpt_ImbanganDuga_4_Vertical_UQ1] UNIQUE NONCLUSTERED
(
[batch_Id] ASC,
[kod] ASC,
[chart_code] ASC,
[year_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
*SQL files with data as attachment - vertical table with data.zip
I've 2 question
1- Below is my SQL Statement using PIVOT
SELECT group_1Digit, group_2Digit, group_3Digit, chart_code, desc_text,
AA, CL, ED, FB, KA, KK, KP, PB, RD , SA
FROM
(
select * from [dbo].[crpt_ImbanganDuga_4_Vertical] where batch_Id='25c7cc7b-d8cc-41ea-8b35-f82951766982'
)
PIVOT
(
SUM(close_amt)
FOR [kod] IN (AA, CL, ED, FB, KA, KK, KP, PB, RD, SA )
) AS P
Looks like, WHERE clause is not correct. I dont what is the correct one to put WHERE clause in PIVOT
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'PIVOT'.
2-After WHERE clause in a proper order, how to make it my SQL statement can produce output in HORIZONTAL FORMAT as follow?
group_1Digit | group_2Digit | group_3Digit | chart_code | desc_text | AA | CL | ED | FB | KA | KK | KP | PB | RD | SA
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10000 | 11000 | 11100 | 11101 | Gaji Biasa K/Tangan UiTM | 2900.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
10000 | 11000 | 11100 | 11101 | Gaji Kakitangan ITM | 0.00 | 0.00 | 87370019.93 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
10000 | 11000 | 11100 | 11101 | Gaji Kakitangan UiTM | 0.00 | 77842607.86 | 0.00 | 62151709.53 | 176240112.88 | 0.00 | 0.00 | 0.00 | 0.00 | 2134572376.37
....
....
....
....
December 30, 2015 at 7:08 pm
The syntax error can be fixed by adding a table alias to the first derived table (shown beloaw as [PivotSource]):
SELECT group_1Digit, group_2Digit, group_3Digit, chart_code, desc_text,
AA, CL, ED, FB, KA, KK, KP, PB, RD , SA
FROM
(
select * from [dbo].[crpt_ImbanganDuga_4_Vertical] where batch_Id='25c7cc7b-d8cc-41ea-8b35-f82951766982'
) AS [PivotSource] -- Add an alias to this derived table
PIVOT
(
SUM(close_amt)
FOR [kod] IN (AA, CL, ED, FB, KA, KK, KP, PB, RD, SA )
) AS P
Eddie Wuerch
MCM: SQL
December 30, 2015 at 7:11 pm
It's works. Thank you Sir
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply