Flatten a table
-
Probably an easy one for most of you out there, but my sql is very rusty.
I have a table with two columns -
PRODUCTID, CUSTOMERID
1 4
1 5
2 9
In the above example, I want to return two rows.
PRODUCTID, CUSTOMERID
1 4,5
2 9
The idea is to group by the first column.
Any help appreciated.
------------------------------
The Users are always right - when I'm not wrong! -
There are a few methods - none of which is that straightforward, but do the job. I've used one method as an example below
Another common method I've seen uses a UDF.
--This SQL script is safe to run
--Create SampleData
DECLARE @SampleData TABLE (PRODUCTID INT, CUSTOMERID INT, CUSTOMERIDS VARCHAR(8000))
INSERT INTO @SampleData (PRODUCTID, CUSTOMERID)
SELECT 1, 4
UNION SELECT 1, 5
UNION SELECT 1, 6
UNION SELECT 2, 9
UNION SELECT 2, 10
UNION SELECT 3, 1
UNION SELECT 3, 5
UNION SELECT 3, 9
UPDATE @SampleData SET CUSTOMERIDS = ''
--Declare and initialise variables
DECLARE @CUSTOMERIDS VARCHAR(8000)
SET @CUSTOMERIDS = ''
DECLARE @PRODUCTID INT
SET @PRODUCTID = ''
--Update CUSTOMERIDS
UPDATE @SampleData SET
@CUSTOMERIDS = CUSTOMERIDS = CASE
WHEN @PRODUCTID = PRODUCTID THEN @CUSTOMERIDS + ', ' + CAST(CUSTOMERID AS VARCHAR(10))
ELSE CAST(CUSTOMERID AS VARCHAR(10)) END,
@PRODUCTID = PRODUCTID
--Select results
SELECT PRODUCTID, MAX(CUSTOMERIDS) AS CUSTOMERIDS
FROM @SampleData
GROUP BY PRODUCTID
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Genius. Thanks for the help. I just need to display a list of mail recipients on one row in an asp page, so this does the job exactly.
------------------------------
The Users are always right - when I'm not wrong! -
Since you are using a front end, why not just return the list to the client and concatenate them in the ASP page?
-
I decided to use a function.
I will pass in the ID and then cursor through appending to a variable that I will then return from the function.
There's never going to be more than a thousand records so I'm not too concerned about performance.
------------------------------
The Users are always right - when I'm not wrong! -
Jonathan,
I'd only recommend using a cursor in this situation if you want poor performance and derision from most people on this site
The usual UDF method uses a little trick, as in the example below (and is a very useful trick to know):
--This SQL script creates and drops 2 objects: tblTempSampleData and fnTempGetProductCustomerIds
--Create table: tblTempSampleData
CREATE TABLE tblTempSampleData (PRODUCTID INT, CUSTOMERID INT)
SET NOCOUNT ON
INSERT INTO tblTempSampleData (PRODUCTID, CUSTOMERID)
SELECT 1, 4
UNION SELECT 1, 5
UNION SELECT 1, 6
UNION SELECT 2, 9
UNION SELECT 2, 10
UNION SELECT 3, 1
UNION SELECT 3, 5
UNION SELECT 3, 9
SET NOCOUNT OFF
GO
--Create function: fnTempGetProductCustomerIds
CREATE FUNCTION fnTempGetProductCustomerIds (@PRODUCTID INT) RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @CUSTOMERIDS VARCHAR(8000)
SET @CUSTOMERIDS = ''
--Update CUSTOMERIDS
SELECT
@CUSTOMERIDS = @CUSTOMERIDS + CAST(CUSTOMERID AS VARCHAR(5)) + ', '
FROM
tblTempSampleData
WHERE
PRODUCTID = @PRODUCTID
SET @CUSTOMERIDS = LEFT(@CUSTOMERIDS, LEN(@CUSTOMERIDS) - 1)
RETURN @CUSTOMERIDS
END
GO
--Select the results using the function
SELECT PRODUCTID, dbo.fnTempGetProductCustomerIds(PRODUCTID) AS CUSTOMERIDS
FROM tblTempSampleData GROUP BY PRODUCTID
--Tidy up
DROP TABLE tblTempSampleData
DROP FUNCTION fnTempGetProductCustomerIds
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thanks Ryan.
I will give it a go with your scenario.
------------------------------
The Users are always right - when I'm not wrong! -
I joined the customer table in the function to return a list of customer names rather than IDs and it works perfectly.
I am now able to display an asp page going through 3 different linked tables using the technique.
Thanks again for the help.
------------------------------
The Users are always right - when I'm not wrong! -
And thanks for the feedback
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply