November 21, 2016 at 9:30 am
what i am doing wrong here.
SELECT BUSINESS_NAME,CONTRACT_CATEGORY,[RX],[PRODUCT]
FROM (
SELECT
BUSINESS_NAME ,
CONTRACT_CATEGORY ,
VALUE,
NAME
FROM PRODUCT_CAT)UP
pivot (SUM(VALUE) FOR NAME IN (['RX'],['PRODUCT']))AS S
i am getting invalid column name rx and product.
November 21, 2016 at 9:47 am
Quick suggestion, add the column names to the CTE
😎
SELECT BUSINESS_NAME,CONTRACT_CATEGORY,[RX],[PRODUCT]
FROM (
SELECT
BUSINESS_NAME ,
CONTRACT_CATEGORY ,
VALUE,
NAME
FROM PRODUCT_CAT)UP (BUSINESS_NAME,CONTRACT_CATEGORY,[RX],[PRODUCT])
pivot (SUM(VALUE) FOR NAME IN (['RX'],['PRODUCT']))AS S
November 21, 2016 at 9:47 am
November 21, 2016 at 9:51 am
You could also try a cross tabs approach.
SELECT BUSINESS_NAME,
CONTRACT_CATEGORY,
SUM( CASE WHEN NAME = 'RX' THEN VALUE ELSE 0 END) AS RX,
SUM( CASE WHEN NAME = 'PRODUCT' THEN VALUE ELSE 0 END) AS [PRODUCT]
FROM (
SELECT
BUSINESS_NAME ,
CONTRACT_CATEGORY ,
SUM(VALUE) AS VALUE,
NAME
FROM PRODUCT_CAT
GROUP BY BUSINESS_NAME ,
CONTRACT_CATEGORY ,
NAME)UP
GROUP BY BUSINESS_NAME ,
CONTRACT_CATEGORY;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply