Need all row IDs even though they''re grouped... how?!
-
Hi all,
Given the query below...
select Sum(Price), Max(PurchaseCurrency)
from ShoppingCartItems
where PurchaseCurrency is not null
group by PurchaseCurrency
...how could I also get a text field containing all the ItemIDs (the ID column of ShoppingCartItems) for the grouped rows? I'd like to get an extra field back with something like this:
"10245;10246;10335;106678;...etc" which are all the ItemID values in that currency group.
I'm not exactly a newbie at SQL Server but this one's got me beat! I've tried all sort of things but have had no luck so far. This *must* be simple so if anyone can point me gently in the right direction I'd appreciate it greatly.
TIA...
Mike
-
I think there is no direct command in SQL that will do what you require. I think you will have to create a new table and insert the result of your query. That new table should also have the the text field but initially will have NULL value. You will then have to create a query that will loop through ShoppingCartItem table to concatenate ID Column by PurchaseCurrency and insert the result to another table. You will then have to create an update query that will update the text field in the new table with the concatenated ID in another table by joining PurchaseCurrency.
-
Thanks very much for the input Reymund - it's really appreciated.
I did the first part ok (inserting the result of the grouped query into a new table) but the 2nd part ("create a query that will loop through ShoppingCartItem table to concatenate ID Column by PurchaseCurrency and insert the result to another table") I just can't seem to get working. The problem is really that my query is much more complicated than what I posted above (that was just to illustrate what I wanted to do at a very basic level) and I can't seem to get the 2nd part to work. Here's what I have so far:
** PART ONE - WORKS FINE **
CREATE TABLE #CartItemsGrouped
(
Ident INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PurchaseCurrency CHAR(3) NOT NULL,
ArtistID INT NOT NULL,
IsPublished INT NOT NULL,
MPGrossAmount DECIMAL(9,2) NOT NULL,
TrackCount INT NOT NULL,
ArtistEmail NVARCHAR(50) NOT NULL,
APRARef NVARCHAR(50),
ItemIDs VARCHAR(1000) NOT NULL
)
DECLARE @iStartDate datetime
DECLARE @iFinishDate datetime
DECLARE @iCurrencyCode varchar(3)
SELECT @iStartDate = '2005-09-21 00:00:00.000'
SELECT @iFinishDate = '2005-09-28 23:59:59.000'
SELECT @iCurrencyCode = 'AUD'
INSERT #CartItemsGrouped
(
PurchaseCurrency,
ArtistID,
IsPublished,
MPGrossAmount,
TrackCount,
ArtistEmail,
APRARef,
ItemIDs
)
SELECT
MAX(ci.purchasecurrency) PurchaseCurrency,
MAX(ci.artistid) ArtistID,
MAX(w.ispublished) ispublished,
SUM(ci.purchaseprice) MPGrossAmount,
COUNT(*) as TrackCount,
MAX(ad.A_Email) ArtistEmail,
MAX(a.Reference) APRARef,
'' as ItemIDs
FROM
ShoppingCartItems ci,
PayPalTransactions pp,
Works w,
ArtistsDetails ad,
Artists a
WHERE pp.cartid = ci.cartid
AND pp.PaymentResult = 'COMPLETED'
AND pp.PaymentDate >= @iStartDate
AND pp.PaymentDate < @iFinishDate
AND ci.PurchaseCurrency = @iCurrencyCode
AND ci.ArtistID = ad.ArtistID
AND ci.PurchasePrice > 0
AND ad.ArtistID = a.ArtistID
AND ci.workid = w.workid
GROUP BY ci.purchasecurrency, ci.artistid, a.Reference, w.ispublished
For the test dates and currency ($AUD) I'm using that gives me 7 rows with all the data I need grouped perfectly plus the empty "ItemIDs" column. For each row I now want to get the ci.ItemID's that make up that group but this is where get stuck. I take it I need to repeat something very similiar to the above to end up with another table of 7 rows with just "Ident" and the concatenated ItemIDs field but everything I've tried has failed so if you could give me a rough idea of what I need to do I'd be VERY grateful!
(I know you don't know our database or data but what we're trying to do is get a batch of sales items from the ShoppingCartItems table, grouped by shopping cart currency, artist ID (and the other two fields) into groups to pass, as a batch, to the PayPal MassPay API so we can automatically pay Artists for sales they get of music they sell on our site. I need to know the individual ItemIDs that make up each group so once the MassPay batch has run OK I can update each ShoppingCartItem with the reference returned from MassPay for that particular batch... this way I know whether the artist has already been paid for that item next time I run our automatic payments service runs.)
Hope I haven't confused you even more!! Many thanks again if you (or anyone) can spare a few moments to give me an idea of how to do this... I have to admit my SQL Server skills are a bit basic at times!
Mike
-
Hi, I think the solution to this requires more than just 1 step or query. I had a bit similar requirement before that also gave me a hard time. But your requirement is bit more complicated
. I asked my friends who are really good in SQL but they said there was really no direct command or one step solution to this type of requirement. I have a very long solution to this. I hope this helps.STEP 1: Create ShoppingCartItemsDetail. This table will contain the data of your original query but ungrouped and unfiltered. This table will be used later to prepare the data for CartId concatenation.
CREATE TABLE [dbo].[ShoppingCartItemsDetail] (
[CartId] [int] NULL ,
[PurchaseCurrency] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ArtistId] [int] NULL ,
[Reference] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsPublished] [smallint] NULL ,
[PurchasePrice] [money] NULL ,
[PaymentResult] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaymentDate] [datetime] NULL ,
[A_Email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
STEP 2: Create CartItemsGrouped table. Please note that ItemIds must be nullable as this table will be updated later with the concatenated CartIds/ItemIds
CREATE TABLE [dbo].[CartItemsGrouped] (
[Ident] [int] IDENTITY (1, 1) NOT NULL ,
[PurchaseCurrency] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ArtistID] [int] NOT NULL ,
[IsPublished] [int] NOT NULL ,
[MPGrossAmount] [decimal](9, 2) NOT NULL ,
[TrackCount] [int] NOT NULL ,
[ArtistEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[APRARef] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemIDs] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
STEP 3: Create ShoppingCartGroupRecordCounter. This table will be used in preparation for CartID concatenation.
CREATE TABLE [dbo].[ShoppingCartGroupRecordCounter] (
[RecordNumber] [int] NULL ,
[PurchaseCurrency] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ArtistId] [int] NULL ,
[Reference] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsPublished] [smallint] NULL ,
[CartId] [int] NULL
) ON [PRIMARY]
GO
STEP 4: Insert records to ShoppingCartItemsDetail
INSERT INTO ShoppingCartItemsDetail
SELECT
ci.CartId,
ci.PurchaseCurrency,
ci.ArtistId,
a.Reference,
w.IsPublished,
ci.PurchasePrice,
pp.PaymentResult,
pp.PaymentDate,
ad.A_Email
FROM
ShoppingCartItems ci,
PayPalTransactions pp,
Works w,
ArtistsDetails ad,
Artists a
WHERE pp.cartid = ci.cartid
AND ci.ArtistID = ad.ArtistID
AND ci.PurchasePrice > 0
AND ad.ArtistID = a.ArtistID
AND ci.workid = w.workid
STEP 5: Insert records to CartItemsGrouped. This similar to your script.
INSERT INTO CartItemsGrouped
(
PurchaseCurrency,
ArtistID,
IsPublished,
MPGrossAmount,
TrackCount,
ArtistEmail,
APRARef,
ItemIDs
)
SELECT
PurchaseCurrency,
ArtistId,
IsPublished,
SUM(PurchasePrice),
COUNT(*),
MAX(A_Email),
MAX(Reference),
NULL
FROM ShoppingCartItemsDetail
WHERE PaymentResult = 'COMPLETED'
AND PaymentDate >= '2005-09-21 00:00:00.000' -- may be replaced with variable parameter
AND PaymentDate < '2005-09-28 23:59:59.000' -- may be replaced with variable parameter
--AND ci.PurchaseCurrency = @iCurrencyCode -- commented out in this example but may be replaced with variable parameter
AND PurchasePrice > 0
GROUP BY PurchaseCurrency, ArtistId, Reference, IsPublished
STEP 6: Create a script that will populate ShoppingCartGroupRecordCounter table. The goal of this script is to group the records by PurchaseCurrency, ArtistID, Reference and IsPublished and have an identity number per group. Example result is:
Identity PurchaseCurrency ArtistiID Reference IsPublished
1 AUD 1 Ref1 1
2 AUD 1 Ref1 1
1 AUD 1 Ref1 0
2 AUD 1 Ref1 0
/* Declare Variables */
DECLARE @iMinimumSeed int
DECLARE @iMaximumSeed int
/* Clean-up ShoppingCartGroupRecordCounter */
TRUNCATE TABLE ShoppingCartGroupRecordCounter
/* Create #TblGroupedRecords temp table to hold grouped records by PurchaseCurrency, ArtistId, Reference and IsPublished */
CREATE TABLE #TblGroupedRecords
(RecordNumber INT IDENTITY (1,1),
PurchaseCurrency CHAR(3),
ArtistId INT,
Reference NVARCHAR(50),
IsPublished INT,
ItemId INT
)
/* Create #TblDistinctRecords temp table to hold distinct PurchaseCurrency, ArtistId, Reference and IsPublished combination */
CREATE TABLE #TblDistinctRecords
(SeedId INT IDENTITY (1,1),
PurchaseCurrency CHAR(3),
ArtistId INT,
Reference NVARCHAR(50),
IsPublished INT
)
/* Insert distinct PurchaseCurrency, ArtistId, Reference and IsPublished combination to temp table */
INSERT INTO #TblDistinctRecords
(PurchaseCurrency,
ArtistId,
Reference,
IsPublished
)
SELECT DISTINCT PurchaseCurrency,
ArtistId,
Reference,
IsPublished
FROM ShoppingCartItemsDetail
/* Initialise Counters */
SET @iMinimumSeed = (SELECT MIN(SeedId) FROM #TblDistinctRecords)
SET @iMaximumSeed = (SELECT MAX(SeedId)+1 FROM #TblDistinctRecords)
WHILE @iMaximumSeed<>@iMinimumSeed
BEGIN
/*Populate #TblGroupedRecords with records grouped by PurchaseCurrency, ArtistId, Reference and IsPublished */
INSERT INTO #TblGroupedRecords
(PurchaseCurrency,
ArtistId,
Reference,
IsPublished,
ItemId)
SELECT cid.PurchaseCurrency,
cid.ArtistId,
cid.Reference,
cid.IsPublished,
cid.CartId
FROM ShoppingCartItemsDetail cid
INNER JOIN #TblDistinctRecords dr ON cid.PurchaseCurrency = dr.PurchaseCurrency
AND cid.ArtistId = dr.ArtistId
AND cid.Reference = dr.Reference
AND cid.IsPublished = dr.IsPublished
WHERE dr.SeedId = @iMinimumSeed
/* Insert grouped records in to ShoppingCartGroupRecordCounter */
INSERT INTO ShoppingCartGroupRecordCounter
SELECT RecordNumber,
PurchaseCurrency,
ArtistId,
Reference,
IsPublished,
ItemId
FROM #TblGroupedRecords
/* Clean-up entries in #TblGroupedRecords for the next set of grouped records */
TRUNCATE TABLE #TblGroupedRecords
SET @iMinimumSeed = @iMinimumSeed + 1
END
DROP TABLE #TblGroupedRecords
DROP TABLE #TblDistinctRecords
STEP 7: Create a stored procedure that will concatenate CartID values. Stored procedure is necessary as this will be called in a query that uses CURSOR.
IF OBJECT_ID('dbo.PrcConcatenateCartId') IS NOT NULL
DROP PROCEDURE dbo.PrcConcatenateCartId
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.PrcConcatenateCartId
@@cPurchaseCurrency CHAR(3),
@@iArtistId INT,
@@cReference NVARCHAR(50),
@@iIsPublished INT,
@@cFinalCartId VARCHAR(500) OUTPUT
AS
SET NOCOUNT ON
/* Declare variables */
DECLARE @cFinalCartId VARCHAR(500)
DECLARE @cCartIdToAdd VARCHAR (500)
DECLARE @iRecordCounterMax INT
DECLARE @iRecordCounter INT
/* Initialise Counters */
SET @iRecordCounterMax = (SELECT COUNT(*)
FROM ShoppingCartGroupRecordCounter
WHERE PurchaseCurrency = @@cPurchaseCurrency
AND ArtistId = @@iArtistId
AND Reference = @@cReference
AND IsPublished = @@iIsPublished)
SET @iRecordCounter = 1
SET @cFinalCartId = ''
/* Loop to concatenate CartId */
WHILE @iRecordCounter <> (@iRecordCounterMax + 1)
BEGIN
/*Initialise @cCartIdToAdd */
SET @cCartIdToAdd = NULL
/*Assign the CartId value of the current record to @cCartIdToAdd */
SET @cCartIdToAdd = (SELECT CAST(CartId AS VARCHAR(500))
FROM ShoppingCartGroupRecordCounter
WHERE PurchaseCurrency = @@cPurchaseCurrency
AND ArtistId = @@iArtistId
AND Reference = @@cReference
AND IsPublished = @@iIsPublished
AND RecordNumber = @iRecordCounter)
/* If the current record has only one CartId then there should be no delimiter */
IF @iRecordCounterMax = 1
BEGIN
SET @cFinalCartId = @cCartIdToAdd
END
ELSE
BEGIN
/* If the current record is on the last CartId value, stop appending delimiter */
IF @iRecordCounter = @iRecordCounterMax
BEGIN
SET @cFinalCartId = @cFinalCartId + @cCartIdToAdd
END
ELSE
/* If the current record is not yet on the last CartId value, append delimiter */
BEGIN
SET @cFinalCartId = @cFinalCartId + @cCartIdToAdd + '; '
END
END
/* Increment Record Counter */
SET @iRecordCounter = @iRecordCounter + 1
END
/*Return Concatenated CartId */
SET @@cFinalCartId = @cFinalCartId
GO
STEP 8: Create a query that will update the ItemIDs in CartItemsGrouped table with the concatenated CartId.
/* Declare variables */
DECLARE @@cPurchaseCurrency CHAR(3)
DECLARE @@iArtistId INT
DECLARE @@cReference NVARCHAR(50)
DECLARE @@iIsPublished INT
DECLARE @@cFinalCartId VARCHAR(500)
/* Declare Cursor */
DECLARE CartId_Cursor CURSOR FOR
SELECT PurchaseCurrency,
ArtistId,
APRARef,
IsPublished
FROM CartItemsGrouped
OPEN CartId_Cursor
FETCH NEXT FROM CartId_Cursor
INTO @@cPurchaseCurrency,
@@iArtistId,
@@cReference,
@@iIsPublished
WHILE @@FETCH_STATUS = 0
BEGIN
/* Execute PrcConcatenateCartId to concatenate CartId for the given record */
EXECUTE PrcConcatenateCartId @@cPurchaseCurrency,
@@iArtistId,
@@cReference,
@@iIsPublished,
@@cFinalCartId OUTPUT
/* Update CartItemsGrouped with the concatenated CartId */
UPDATE CartItemsGrouped
SET ItemIds = @@cFinalCartId
WHERE PurchaseCurrency = @@cPurchaseCurrency
AND ArtistId = @@iArtistId
AND APRARef = @@cReference
AND IsPublished = @@iIsPublished
FETCH NEXT FROM CartId_Cursor
INTO @@cPurchaseCurrency,
@@iArtistId,
@@cReference,
@@iIsPublished
END
CLOSE CartId_Cursor
DEALLOCATE CartId_Cursor
This is a sample data in the CartItemsGrouped table after the execution of the steps above. Item IDs now have concatenated CartId values.
Ident PurchaseCurrency ArtistId IsPublished MPGross TrackCount Email Ref ItemIDs
1 AUD 1 0 20 2 email1 Ref1 3; 4
2 AUD 1 1 20 2 email1 Ref1 1; 2
3 SGD 3 0 30 3 email3 Ref3 10; 11; 12
4 USD 2 0 40 2 email2 Ref2 8; 9
5 USD 2 1 60 3 email2 Ref2 5; 6; 7
-
Reymund is correct, there is no such function to do this (who knows why not cause a lot of people want to do this). I have searched for this kind of behaviour and you do have to do some weird looping to achieve the result.
(I forget the exact references, I used Google)
However, all of the coders that have a solution for this highly recommend NOT doing this in SQL unless you have no other choice.
If this will be a stored procedure that is referenced from something else (a webpage for example), then they recommend doing the concatenation at the ASP (or equiv.) level unless there is a reason you can't.
The web programmer that was wanting to do that at my work took this approach and had it working in less that 5 minutes.
Hope that gives you some additional insite into potential alternatives for a solution to your problem.
Hrmm.. that last line is almost Dr Suess like.
-
Guys, thanks so much for the input... Reymund especially, I deeply appreciate the time and effort you've put into helping me out.
In the end I decided it was going to be far too difficult to do it this way and instead realised that as long as I could identify all the matching rows with a common GUID I could then pass that back to my C# app, do the MassPay run, then, depending on the success of that, update a SUCCESS column where the ShoppingCartItems ItemId = the GUID from above.
This has turned out to be a fairly simple way so just for completeness I've posted the stored proc below...
CREATE PROCEDURE GetMassPayTransactionsByDateAndCurrency
@iStartDate datetime,
@iFinishDate datetime,
@iCurrencyCode varchar(3)
AS
--
-- First create a temporary table to store all the ShoppingCartItems that are
-- to be part of this MassPay batch.
--
CREATE TABLE #CartItemConcat
(
ItemID int,
CartID int,
CollectionID int,
PurchaseCurrency CHAR(3) NOT NULL,
ArtistID INT NOT NULL,
IsPublished INT NOT NULL,
MPGrossAmount DECIMAL(9,2) NOT NULL,
ArtistEmail NVARCHAR(50) NOT NULL,
APRARef NVARCHAR(50),
MassPayBatchID uniqueidentifier
)
--
-- Create a new GUID to identify the ShoppingCartItems rows in this MassPay batch.
--
DECLARE @MPid uniqueidentifier
SET @MPid = NEWID()
--
-- Now extract the matching ShoppingCartItems rows to add to this MassPay batch.
--
INSERT #CartItemConcat
(
ItemID,
CartID,
CollectionID,
PurchaseCurrency,
ArtistID,
IsPublished,
MPGrossAmount,
ArtistEmail,
APRARef,
MassPayBatchID
)
SELECT
ci.ItemID,
ci.CartID,
ci.CollectionID,
ci.purchasecurrency PurchaseCurrency,
ci.artistid,
w.ispublished,
ci.purchaseprice MPGrossAmount,
ad.A_Email ArtistEmail,
a.Reference APRARef,
@MPid
FROM
ShoppingCartItems ci,
PayPalTransactions pp,
Works w,
ArtistsDetails ad,
Artists a
WHERE
pp.cartid = ci.cartid
AND pp.PaymentResult = 'COMPLETED'
AND pp.PaymentDate >= @iStartDate
AND pp.PaymentDate < @iFinishDate
AND ci.PurchaseCurrency = @iCurrencyCode
AND ci.ArtistID = ad.ArtistID
AND ci.PurchasePrice > 0
AND ad.ArtistID = a.ArtistID
AND ci.workid = w.workid
ORDER BY ci.purchasecurrency, ci.artistid, a.Reference, w.ispublished
--
-- Update the relevant rows in ShoppingCartItems with the MassPay batch ID guid.
--
UPDATE ShoppingCartItems
SET MassPayBatchID = @MPid
FROM ShoppingCartItems s, #CartItemConcat c
WHERE s.ItemId = c.ItemID
--
-- Summarise the data from the temporary table into grouped MassPay
-- transactions to pass back to the calling .Net application.
--
SELECT
MAX(purchasecurrency) PurchaseCurrency,
MAX(artistid) ArtistID,
MAX(ispublished) IsPublished,
SUM(MPGrossAmount) MPGrossAmount,
COUNT(*) as TrackCount,
MAX(ArtistEmail) ArtistEmail,
MAX(APRARef) APRARef,
@MPid
FROM #CartItemConcat
GROUP BY purchasecurrency, artistid, APRARef, ispublished
GO
Cheers, and thanks again for the help!!
Mike
-
PS: Reymund, sorry I didn't get this posted in time to save all that effort on your part - I had to change the way I did a few other things before I could get my GUID method working and have only *just* got it all working the way I want it.
Mike
-
That's an impressive amount of code!
I can only assume that it works!
Surely though it could be done a bit more simply with something like (I usually avoid UDFs and was hoping to get some nice table joins with derived tables going, but the itemID concatenation is the sticky bit - you need a variable!)
create function fnIDs(@Curr varchar(10)) returns varchar(8000) AS begin declare @R varchar(8000) set @R = '' select @R = @R + cast(ID as varchar(10)) + ';' from ShoppingCartItems where PurchaseCurrency = @Curr
return @R end GO
select PurchaseCurrency, sum(price), dbo.fnIDs(PurchaseCurrency) as ItemIDs from ShoppingCartItems where PurchaseCurrency is not null group by PurchaseCurrency
I think that should work? It's untested but should be easier to debug / understand if it's not right.
Cheers,
Ian
-
Ian,
I tried that but in the function I needed to have a 'GROUP BY' clause to get the ID's for each disparate group and as soon as I did that I lost the abliity to use the ItemID column without an aggregate. If you look at my 2nd post you'll see my actual requirements were much more complex than I initially posted - I thought the answer would be very easy so only posted a simple example at the start in order not to over-complicate it and scare people away from helping!
'preciate the input though!
Mike
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply