July 23, 2012 at 4:47 pm
To all the SQL Pros out there...
For this issue, I am attempting to implement set-based thinking. However, I'm not sure how to get around using variables with a while loop to do this using string values. I've done it using aggregates with numeric fields, but was wondering if there was a more elegant solution than this.
I've done plenty of searches for this, so if you have related article links, that would also be greatly appreciated.
Given the table:
table_id field_id string
-------- -------- ------
1 1 A
2 1 B
3 1 C
4 2 D
5 2 E
6 3 F
7 3 G
8 3 H
9 3 I
I want to insert the following results into another table:
field_id string
-------- ------
1 Result: A, B, C
2 Result: D, E
3 Result: F, G, H, I
Here is my current solution. I am using temp tables instead of table variable, so if a better solution can be gained that way I am flexible on the implementation.
SQL Code:
-- Declare the variables
DECLARE @tblInput TABLE
( table_id INT,
field_id INT,
sequence_id INT,
value CHAR(1) );
DECLARE @tblOutput TABLE
( field_id INT,
string VARCHAR(50) );
DECLARE @intIndex INT,
@intFieldID INT,
@intReturnString VARCHAR(50),
@intSequenceID INT;
-- Set the default variable values
SET @intIndex = 1;
SET @intFieldID = 0;
SET @intReturnString = '';
SET @intSequenceID = 0;
/* STEP 1: Populate the Input table */
INSERT INTO @tblInput
( table_id, field_id, value )
SELECT 1, 1, 'A'
UNION ALL
SELECT 2, 1, 'B'
UNION ALL
SELECT 3, 1, 'C'
UNION ALL
SELECT 4, 2, 'D'
UNION ALL
SELECT 5, 2, 'E'
UNION ALL
SELECT 6, 3, 'F'
UNION ALL
SELECT 7, 3, 'G'
UNION ALL
SELECT 8, 3, 'H'
UNION ALL
SELECT 9, 3, 'I';
-- Set the sequence id, using the request_workflow_id as a grouping
UPDATE @tblInput
SET @intSequenceID = sequence_id =
CASE
WHEN @intFieldID = field_id THEN @intSequenceID + 1
ELSE 1
END,
@intFieldID = field_id;
-- Review the Input table
SELECT * FROM @tblInput;
/* STEP 2: Build the output table */
-- This loop adds the string value for each field_id to the Output table
WHILE @intIndex <= (SELECT MAX(field_id) FROM @tblInput)
BEGIN
-- Build the assignment string for each request workflow
SELECT @intFieldID = field_id,
@intReturnString = CASE sequence_id
WHEN 1 THEN 'Result: ' + value
ELSE @intReturnString + ', ' + value
END
FROM @tblInput
WHERE field_id = @intIndex;
-- Insert the assignment string for this request workflow
INSERT INTO @tblOutput(field_id, string)
VALUES (@intFieldID, @intReturnString);
SET @intReturnString = '';
SET @intIndex = @intIndex + 1;
END;
-- Review the Output table
SELECT * from @tblOutput
July 23, 2012 at 5:21 pm
If you remove the commas from the examples, the following article explains one fast and set based way to do this.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2012 at 5:25 pm
If you mean something like this:
DECLARE @tblInput TABLE
( table_id INT,
field_id INT,
sequence_id INT,
value CHAR(1) );
/* STEP 1: Populate the Input table */
INSERT INTO @tblInput
( table_id, field_id, value )
SELECT 1, 1, 'A'
UNION ALL
SELECT 2, 1, 'B'
UNION ALL
SELECT 3, 1, 'C'
UNION ALL
SELECT 4, 2, 'D'
UNION ALL
SELECT 5, 2, 'E'
UNION ALL
SELECT 6, 3, 'F'
UNION ALL
SELECT 7, 3, 'G'
UNION ALL
SELECT 8, 3, 'H'
UNION ALL
SELECT 9, 3, 'I';
with BaseData as (
select distinct
field_id
from
@tblInput
)
select
bd.field_id,
stuff((select ',' + bd1.value
from @tblInput bd1
where bd.field_id = bd1.field_id
for xml path(''),type).value('.','varchar(max)'),1,1,'') as ConCatField
from
BaseData bd;
July 24, 2012 at 7:13 am
Thank you, Jeff and Lynn for pointing me in the right direction. Now I'm off to brush up on my XML...:cool:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply