March 13, 2012 at 6:18 pm
Hi,
This is driving me mad. I want to take some rows of data and put them into 1 row. There will be a max of 4 rows concatenated. Here are the tables:
CREATE TABLE [dbo].[ResultSupp](
[RSID] [int] IDENTITY(1,1) NOT NULL,
[RS_FK_ResultID] [int] NOT NULL,
[RS_FK_SuppID] [int] NOT NULL,
[RS_Score] [bit] NOT NULL CONSTRAINT [DF_ResultSupp_RS_Score] DEFAULT ((0)),
[RS_Active] [bit] NOT NULL CONSTRAINT [DF_ResultSupp_RS_Active] DEFAULT ((1)),
[RS_LastModified] [datetime] NOT NULL CONSTRAINT [DF_ResultSupp_RS_LastModified] DEFAULT (getdate()),
) ON [PRIMARY]
CREATE TABLE [dbo].[Results](
[ResultID] [int] IDENTITY(1000,1) NOT NULL,
[R_FK_LayoutID] [int] NOT NULL,
[RScore] [int] NULL,
[RComment] [varchar](50) NULL,
[R_FK_HeaderID] [int] NOT NULL,
[R_Active] [bit] NOT NULL CONSTRAINT [DF_Results_R_Active] DEFAULT ((1)),
[R_LastModified] [datetime] NOT NULL CONSTRAINT [DF_Results_R_LastModified] DEFAULT (getdate()),
) ON [PRIMARY]
CREATE TABLE [dbo].[TestLayout](
[LayoutID] [int] IDENTITY(1,1) NOT NULL,
[LDescription] [varchar](100) NOT NULL,
[LMinDesc] [varchar](50) NOT NULL,
[LMaxDesc] [varchar](50) NOT NULL,
[L_FK_TTypeID] [int] NOT NULL,
[LActive] [bit] NOT NULL CONSTRAINT [DF_TestLayout_LActive] DEFAULT ((1)),
[LLastModified] [datetime] NOT NULL CONSTRAINT [DF_TestLayout_LLastModified] DEFAULT (getdate()),
[LPageID] [int] NOT NULL,
[SortOrder] [int] NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[TestSupplement](
[SuppID] [int] IDENTITY(1,1) NOT NULL,
[SuppDescription] [varchar](30) NOT NULL,
[SuppActive] [bit] NOT NULL CONSTRAINT [DF_TestSupplement_SuppActive] DEFAULT ((1)),
[SuppLastModified] [datetime] NOT NULL CONSTRAINT [DF_TestSupplement_SuppLastModified] DEFAULT (getdate()),
[Supp_FK_LayoutID] [int] NOT NULL,
[SortOrder] [int] NOT NULL,
) ON [PRIMARY]
Join COde:
FROM TestSupplement INNER JOIN
ResultSupp ON ResultSupp.RS_FK_SuppID = TestSupplement.SuppID INNER JOIN
TestLayout ON TestSupplement.Supp_FK_LayoutID = TestLayout.LayoutID
WHERE (ResultSupp.RS_FK_ResultID = @ResultID AND TestLayout.LayoutID = @LayoutID)
What I want to return is Result.ResultID, TestLayout.LDescription + ': ' + TestSupplement.SuppDescription in a row for each ResultID
I've tried this (but I can't use the output in a Crystal Report)
SELECT TestLayout.LDescription + ': ' + TestSupplement.SuppDescription + ', ' AS 'data()'
FROM TestSupplement INNER JOIN
ResultSupp ON ResultSupp.RS_FK_SuppID = TestSupplement.SuppID INNER JOIN
TestLayout ON TestSupplement.Supp_FK_LayoutID = TestLayout.LayoutID
WHERE (ResultSupp.RS_FK_ResultID = @ResultID AND TestLayout.LayoutID = @LayoutID) for xml path('')
Any ideas?
Thanks.
March 13, 2012 at 9:34 pm
There are lot of tables in the mix. I'm confused. But here is the code i use when i need to concatenate multiple rows into one single row.
IF OBJECT_ID('TEMPDB..#CONCAT_COLUMN_VALUES') IS NOT NULL
DROP TABLE #CONCAT_COLUMN_VALUES
CREATE TABLE #CONCAT_COLUMN_VALUES
(
id INT,
COL_VAL VARCHAR(5)
)
INSERT INTO #CONCAT_COLUMN_VALUES
SELECT 1, 'A' UNION ALL
SELECT 1, 'B' UNION ALL
SELECT 1, 'C' UNION ALL
SELECT 2, 'D' UNION ALL
SELECT 2, 'E' UNION ALL
SELECT 3, 'F' UNION ALL
SELECT 4, 'G'
SELECT p1.id,
STUFF ( ( SELECT ','+COL_VAL
FROM #CONCAT_COLUMN_VALUES p2
WHERE p2.id = p1.id
ORDER BY COL_VAL
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
FROM #CONCAT_COLUMN_VALUES p1
GROUP BY p1.id ;
Tweak it to make it work for you!
March 14, 2012 at 4:54 am
Hi,
I've got this working but not fully happy with solution. I made a Temporary table and put the results that I wanted to add together into this first to make it a bit easier to follow. Here's what I now have (borrowed from http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/):
ALTER FUNCTION dbo.CollateSuppComments(@R_FK_HeaderID int
,@LPageID int
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @ReturnSuppComments varchar(1000)
DECLARE @TempComments TABLE (
R_FK_HeaderID int
,CommentDesc varchar(1000)
,LPageID int
)
INSERT INTO @TempComments(
R_FK_HeaderID
,CommentDesc
,LPageID
)
SELECT R_FK_HeaderID
, TestLayout.LDescription + ': ' + TestSupplement.SuppDescription AS CommentDesc
,TestLayout.LPageID
FROM TestSupplement INNER JOIN
ResultSupp AS p1 ON p1.RS_FK_SuppID = TestSupplement.SuppID INNER JOIN
TestLayout ON TestSupplement.Supp_FK_LayoutID = TestLayout.LayoutID INNER JOIN
Results ON p1.RS_FK_ResultID = Results.ResultID AND TestLayout.LayoutID = Results.R_FK_LayoutID
WHERE R_FK_HeaderID = @R_FK_HeaderID AND LpageID = @LPageID AND RS_Score = 1
SELECT @ReturnSuppComments =
MAX( CASE seq WHEN 1 THEN CommentDesc ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 2 THEN CommentDesc ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 3 THEN CommentDesc ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 4 THEN CommentDesc ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 5 THEN CommentDesc ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 6 THEN CommentDesc ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 7 THEN CommentDesc ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 8 THEN CommentDesc ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 9 THEN CommentDesc ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 10 THEN CommentDesc ELSE '' END )
FROM ( SELECT p1.R_FK_HeaderID, p1.CommentDesc,
( SELECT COUNT(*)
FROM @TempComments p2
WHERE p2.R_FK_HeaderID = p1.R_FK_HeaderID
AND p2.LPageID <= p1.LPageID AND
p2.CommentDesc <= p1.CommentDesc )
FROM @TempComments p1 ) D ( R_FK_HeaderID, CommentDesc, seq )
GROUP BY R_FK_HeaderID
RETURN @ReturnSuppComments
END
Two questions:
1. What does this line do:
D ( R_FK_HeaderID, CommentDesc, seq )
2. Is there a way to not add the extra commas if there isn't a corresponding seq i.e. if there are only three comments to add together, 7 commas are added to the end, coming from here:
MAX( CASE seq WHEN 1 THEN CommentDesc ELSE '' END ) + ', ' +
Thanks
March 14, 2012 at 6:31 am
dec_obrien (3/14/2012)
Is there a way to not add the extra commas if there isn't a corresponding seq
Yes. Do what ColdCoffee said in the first place.
Untested, but it should be something like this: -
ALTER FUNCTION dbo.CollateSuppComments (@R_FK_HeaderID INT, @LPageID INT)
RETURNS VARCHAR(1000)
AS
BEGIN
SELECT @ReturnSuppComments =
STUFF((SELECT ', ' + CommentDesc
FROM (SELECT R_FK_HeaderID, TestLayout.LDescription + ': ' + TestSupplement.SuppDescription AS CommentDesc
FROM TestSupplement
INNER JOIN ResultSupp AS p1 ON p1.RS_FK_SuppID = TestSupplement.SuppID
INNER JOIN TestLayout ON TestSupplement.Supp_FK_LayoutID = TestLayout.LayoutID
INNER JOIN Results ON p1.RS_FK_ResultID = Results.ResultID AND TestLayout.LayoutID = Results.R_FK_LayoutID
WHERE R_FK_HeaderID = @R_FK_HeaderID AND LpageID = @LPageID AND RS_Score = 1) a
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '');
RETURN @ReturnSuppComments
END
March 14, 2012 at 8:17 am
Hi,
Yes it works (although I've no idea why!).
THanks
March 14, 2012 at 8:25 am
dec_obrien (3/14/2012)
Hi,Yes it works (although I've no idea why!).
THanks
I'll try an example that you can follow in your SQL window
First, set your results to text and execute this: -
SELECT TOP 5 t1.[name]
FROM sys.columns t1
INNER JOIN sys.columns t2 on t1.[column_id] = t2.[column_id]
WHERE t1.[column_id] = t2.[column_id]
FOR XML PATH
You'll get back something like this: -
<row><name>addr</name></row><row><name>addr</name></row><row><name>addr</name></row><row><name>addr</name></row><row><name>addr</name></row>
Next, we'll elimate the row tags, which is done simply by adding ('')
SELECT TOP 5 t1.[name]
FROM sys.columns t1
INNER JOIN sys.columns t2 on t1.[column_id] = t2.[column_id]
WHERE t1.[column_id] = t2.[column_id]
FOR XML PATH('')
Now we have this: -
<name>addr</name><name>addr</name><name>addr</name><name>addr</name><name>addr</name>
Now, we'll add a comma that comes before the result. This makes the result into a string.
SELECT TOP 5 ', ' + t1.[name]
FROM sys.columns t1
INNER JOIN sys.columns t2 on t1.[column_id] = t2.[column_id]
WHERE t1.[column_id] = t2.[column_id]
FOR XML PATH('')
, addr, addr, addr, addr, addr
STUFF allows us to get rid of the comma at the beginning, giving us a list.
SELECT STUFF((SELECT TOP 5 ', ' + t1.[name]
FROM sys.columns t1
INNER JOIN sys.columns t2
ON t1.[column_id] = t2.[column_id]
WHERE t1.[column_id] = t2.[column_id]
FOR XML PATH('')), 1, 2, '')
Which gives us this: -
addr, addr, addr, addr, addr
Does that help?
March 14, 2012 at 10:48 am
Helps a lot. Thank you for going to the trouble of explaining it. And if you're up for one last question, teh last line of my previous solution containd the following:
FROM @TempComments p1 ) D ( R_FK_HeaderID, CommentDesc, seq )
What does that do?
March 14, 2012 at 11:11 am
FROM @TempComments p1 ) D ( R_FK_HeaderID, CommentDesc, seq )
D is called table-alias.. This is dynamically assinging a name to a sub-query so that i can be referenced elswhere in the query. And the values in the brackets are the column names that the table-alias is going to contain.
To know more about tables aliases, read here Using Table Aliases MSDN
Thanks to cadavre for explaining the XML so lucidly, i couldnt have done it better!
March 14, 2012 at 11:17 am
Spot on guys.
Great help and thanks again!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply