January 28, 2015 at 9:13 am
Hi,
I have a table where I need to concatenate and sum based on conditions given below .
CREATE TABLE #DimOrder
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[OrderID] [INT] NOT NULL,
[SalesRegion] [VARCHAR](5) NULL,
[OrderType] [INT] NOT NULL,
[Projectid] [INT] NULL,
[ProductID] [INT] NULL,
[FormatID] [INT] NULL,
[MEdiaTypeID] [INT] NULL,
[ProductComment] [VARCHAR](MAX) NULL,
[UOM] [VARCHAR](80) NULL,
[FormatName] [VARCHAR](100) NULL,
[Media] [CHAR](20) NULL,
[AppliedRate] [DECIMAL](12, 5) NULL
)
INSERT INTO #DimOrder (
OrderID,
SalesRegion,
OrderType,
Projectid,
ProductID,
FormatID,
MEdiaTypeID,
ProductComment,
UOM,
FormatName,
Media,
AppliedRate) VALUES (
1296
, 'SA'
, 2
, 2547
, 364
, 6
, 66
, 'Fast Track'
, 'sq mi'
, 'Y'
, 'LTO4_800 '
, 41.67
)
INSERT INTO #DimOrder (
OrderID,
SalesRegion,
OrderType,
Projectid,
ProductID,
FormatID,
MEdiaTypeID,
ProductComment,
UOM,
FormatName,
Media,
AppliedRate) VALUES (
1296
, 'SA'
, 2
, 2547
, 364
, 6
, 66
, 'Final Volume'
, 'sq mi'
, 'Y'
, 'LTO4_800 '
, 41.67
)
INSERT INTO #DimOrder (
OrderID,
SalesRegion,
OrderType,
Projectid,
ProductID,
FormatID,
MEdiaTypeID,
ProductComment,
UOM,
FormatName,
Media,
AppliedRate) VALUES (
1207
, 'AP'
, 2
, 2351
, 364
, 6
, 32
, 'no charge'
, 'sq mi'
, 'Y'
, '3590E '
, 0.00
)
INSERT INTO #DimOrder (
OrderID,
SalesRegion,
OrderType,
Projectid,
ProductID,
FormatID,
MEdiaTypeID,
ProductComment,
UOM,
FormatName,
Media,
AppliedRate) VALUES (
1207
, 'AP'
, 2
, 2351
, 364
, 6
, 32
, 'final volume'
, 'sq mi'
, 'Y'
, '3590E '
, 55.56000
)
SELECT * FROM #DimOrder
DROP TABLE #DimOrder
IF OrderID,PrjectID,ProductID,FormatID,MediaTypeID AND AppliedRate ARE same AND ProductComment IS different , THEN I want TO concetenate ProductComment
--Example
SELECT 1296 AS OrderID, 2547 AS ProjectID,364 AS ProductID,6 AS FormatID,66 AS MEdiaTypeID,'Fast Track,Fast Track' AS ProductComment,'sq mi' AS UOM,'Y' AS FormatName,'LTO4_800' AS MEdia ,41.67000 AS aPPLIEDRATE
IF OrderID,PrjectID,ProductID,FormatID,MediaTypeID AND AppliedRate AND ProductComment IS different , THEN I want TO concetenate ProductComment AND sum AppliedRate
--Example
SELECT 1207 AS OrderID, 2351 AS ProjectID,364 AS ProductID,6 AS FormatID,32 AS MEdiaTypeID,'no charge,final volume' AS ProductComment,'sq mi' AS UOM,'Y' AS FormatName,'3590E' AS MEdia ,55.56000 AS AppliedRate
Please help .
Thanks,
PSB
January 28, 2015 at 9:17 am
use STUFF for the concatenated list and SUM with GROUP BY the result of the concatenation
January 28, 2015 at 11:21 pm
SELECT
Main.OrderID,
Main.SalesRegion,
Main.OrderType,
Main.Projectid,
Main.ProductID,
Main.FormatID,
Main.MEdiaTypeID,
LEFT(Main.COMMENTS, LEN(Main.COMMENTS) - 1) AS 'ProductComment',
MAIN.UOM,
MAIN.FormatName,
MAIN.Media INTO #A
FROM (SELECT DISTINCT
ST2.OrderID,
ST2.SalesRegion,
ST2.OrderType,
ST2.Projectid,
ST2.ProductID,
ST2.FormatID,
ST2.MEdiaTypeID,
ST2.UOM,
ST2.FormatName,
ST2.Media,
(SELECT
ST1.ProductComment + ',' AS [text()]
FROM #dimorder ST1
WHERE ST1.OrderID = ST2.OrderID
ORDER BY ST1.OrderID
FOR xml PATH (''))
COMMENTS
FROM #dimorder ST2) [Main]
------------------
SELECT
T.ORDER_ID,
T.SALESREGION,
T.ORDERTYPE,
T.PROJECTID,
T.PRODUCTID,
T.FORMATID,
T.MEDIATYPEID,
T.UOM,
T.FORMATNAME,
T.MEDIA,
S.PRODUCTCOMMENT,
SUM(T.APPLIEDRATE) APPLIEDRATE
FROM (SELECT DISTINCT
OrderID,
SalesRegion,
OrderType,
Projectid,
ProductID,
FormatID,
MEdiaTypeID,
UOM,
FormatName,
Media,
AppliedRate
FROM #dimorder) t
JOIN (SELECT DISTINCT
order_id,
salesRegion,
OrderType,
Projectid,
ProductID,
FormatID,
MEdiaTypeID,
ProductComment,
UOM,
FormatName,
Media
FROM #a a) S
ON S.ORDER_ID = T.ORDER_ID
GROUP BY T.ORDER_ID,
T.SALESREGION,
T.ORDERTYPE,
T.PROJECTID,
T.PRODUCTID,
T.FORMATID,
T.MEDIATYPEID,
T.UOM,
T.FORMATNAME,
T.MEDIA,
S.PRODUCTCOMMENT
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply