August 4, 2008 at 5:32 am
Hi gurus,
I have two tables as follows :
Product table
[Prod_ID] [Item] [General_Value]
101 ACID 100
102 ROCK 200
103 ORANGE 300
Customer_Table
[Cust_ID] [Prod_ID] [CValue]
110120
110250
1103150
2101150
2102100
2103500
I want the output as follows :
[Prod_ID] [Item] [General_Value] [Cust_Id_1] [Cust_Id_2]
101 ACID 10020150
102 ROCK 20050100
103 ORANGE 300150500
can someone help me please.
August 4, 2008 at 6:28 am
You can use PIVOT on SQL Server 2005. With the above example it would look like:
SELECT [Prod_ID]
, [Item]
, [General_Value]
, [1] AS [Cust_Id_1]
, [2] AS [Cust_Id_2]
FROM ( SELECT Customer_Table.[Prod_ID]
, [Item]
, [General_Value]
, CValue
, [Cust_ID]
FROM Customer_Table
JOIN Product ON Customer_Table.[Prod_ID] = Product.[Prod_ID] ) p
PIVOT ( SUM(CValue) FOR [Cust_ID] IN ( [1], [2] ) ) AS pvt
ORDER BY [Prod_ID] ;
Regards,
Andras
August 4, 2008 at 6:34 am
Andras,
how should the query be modified if 3&4 cust is also to be included as column in the output.
August 4, 2008 at 6:38 am
mathewspsimon (8/4/2008)
Andras,how should the query be modified if 3&4 cust is also to be included as column in the output.
SELECT [Prod_ID]
, [Item]
, [General_Value]
, [1] AS [Cust_Id_1]
, [2] AS [Cust_Id_2]
, [3] AS [Cust_Id_3]
, [4] AS [Cust_Id_4]
FROM ( SELECT Customer_Table.[Prod_ID]
, [Item]
, [General_Value]
, CValue
, [Cust_ID]
FROM Customer_Table
JOIN Product ON Customer_Table.[Prod_ID] = Product.[Prod_ID] ) p
PIVOT ( SUM(CValue) FOR [Cust_ID] IN ( [1], [2], [3], [4] ) ) AS pvt
ORDER BY [Prod_ID] ;
If you do now know how many customers you need, you can generate the above query dynamically. I summarized something similar on http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
Regards,
Andras
August 4, 2008 at 10:22 am
HI Andras
I get the following error
Msg 325, Level 15, State 1, Line 30
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
I am using SQL SERVER 2005
August 4, 2008 at 11:44 am
As always, I'm going to recommend doing pivot operations outside the database. If you absolutely MUST do them in the database, check out the data here:
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/
Phil and Robyn give sample code and very detailed instructions on how it works.
Generally speaking, Excel will out-pivot SQL by a huge margin. Slightly slower, much more powerful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2008 at 1:53 am
mathewspsimon (8/4/2008)
HI AndrasI get the following error
Msg 325, Level 15, State 1, Line 30
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
I am using SQL SERVER 2005
While you may be using SQL Server 2005 your database seems to be set to a compatibility level of 80 (80 is for SQL Server 2000) or earlier. You can check this by executing:
sp_dbcmptlevel 'myDatabaseName'
In order to use all the SQL Server 2005 features you need to be in compatibility mode 90 (90 is for SQl Server 2005)
You can change it by executing:
sp_dbcmptlevel 'myDatabaseName', 90
However, things may break (there are some new reserved keywords, like PIVOT, objects are organised into schemas, etc). There are many articles about upgrading to SQL Server 2005.
If moving to SQL Server 2005 (or actually 90 compatibility mode) is not an option, you can use CASE statements like:
SELECT Customer_Table.Prod_ID, ...
SUM(CASE WHEN Cust_ID=1 THEN CValue ELSE 0 END) AS Cust_Id_1,
SUM(CASE WHEN Cust_ID=2 THEN CValue ELSE 0 END) AS Cust_Id_2,
...
FROM Customer_Table
JOIN Product ON Customer_Table.[Prod_ID] = Product.[Prod_ID]
GROUP BY Cust_ID
Regards,
Andras
August 5, 2008 at 11:06 am
Hi,
All these queries are tied to the Customer ID, If i know these values in advance then i can use PIVOT or CASE to convert the rows into columns, but if i dont know nO.of Custimer IDs then what is the way to do it.
Thanks -- Vj
August 6, 2008 at 2:49 am
Vijaya Kadiyala (8/5/2008)
Hi,All these queries are tied to the Customer ID, If i know these values in advance then i can use PIVOT or CASE to convert the rows into columns, but if i dont know nO.of Custimer IDs then what is the way to do it.
Thanks -- Vj
You can generate the pivot statement dynamically. I summarized an example on http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
Regards,
Andras
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply