SQL Server 2008 - Concatenate String
I have the need to take the results of a query per line item and build a semi-colon delimited list of materials that ma开发者_StackOverflow社区ke up that item.
Schema Makeup:
Tables: LineItems (Unique Item Listing) LineItems_Materials (Many to Many) Materials (Unique Material Listing)
Line Items: ID|LineItem 1|'1A.1'
LineItems_Materials: ID|LineItemID|MaterialID 1|1|1 2|1|2 3|1|3
Materials: ID|Material 1|Concrete 2|Steel 3|Dirt
So For Line Item 1 (1A.1) I want it to show Concrete;Steel;Dirt
I know I can write a Function to do this. I used CTE in the function....I could use a while loop as well. Is there another method that would be better?
Here is what I have (Script will build objects, Load data, and Create Function):
SCRIPT:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[UFN_LineItem_Materials]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [test].[UFN_LineItem_Materials]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[LineItems]') AND type in (N'U'))
DROP TABLE [test].[LineItems]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Materials]') AND type in (N'U'))
DROP TABLE [test].[Materials]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[LineItems_Materials]') AND type in (N'U'))
DROP TABLE [test].[LineItems_Materials]
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'test')
DROP SCHEMA [test]
GO
CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO
Create Table test.Materials(
MaterialID INT IDENTITY(1,1),
Material varchar(100));
Insert Into test.Materials
Values('Concrete');
Insert Into test.Materials
Values('Steel');
Insert Into test.Materials
Values('Dirt');
GO
Create Table test.LineItems_Materials(
LineItemMaterialID INT IDENTITY(1,1),
LineItemID INT,
MaterialID INT)
GO
Insert Into test.LineItems_Materials
Select 1,1
UNION
Select 1,2
UNION
Select 1,3
GO
CREATE TABLE [test].[LineItems](
[LineItemID] [int] IDENTITY(1,1) NOT NULL,
[ItemNumber] [varchar](25) NULL
) ON [PRIMARY]
GO
Insert Into [test].[LineItems]
Select '1A.1'
GO
-------------------------------------------------------------
--Build Material Strings (;) example: List of Materials
------------------------------------------------------------
CREATE FUNCTION test.UFN_LineItem_Materials(@LineItemID INT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Materials Varchar(100) = ''
;with CTE
AS(
Select lm.LineItemID,m.MaterialID,m.Material
from test.LineItems_Materials lm
inner join test.Materials m on lm.MaterialID = m.MaterialID
Where lm.LineItemID = @LineItemID
)
Select @Materials += ';' + c.Material
from CTE c;
SET @Materials = substring(@Materials,2,LEN(@Materials)-1);
RETURN @Materials;
END
GO
Select lm.LineItemID,test.UFN_LineItem_Materials(lm.LineItemID) Materials
From test.Materials m
inner join test.LineItems_Materials lm on m.MaterialID = lm.MaterialID
Where m.Material = 'Concrete'
Any other ideas?
Always appreciate the feedback
--S
If you want to concatenate values across rows, use the FOR XML trick, e.g.:
SELECT Name + ','
FROM Project
FOR XML PATH('')
Here is a more complete example:
select LineItemID, (
Select m.Material + ','
From test.Materials m
inner join test.LineItems_Materials lm1 on m.MaterialID = lm1.MaterialID
Where m.MaterialID in (select MaterialID from test.LineItems_Materials where LineItemID = lm2.LineItemID)
FOR XML PATH('')
) as Materials
from test.LineItems_Materials lm2
group by LineItemID
try something like this:
declare @x table (x varchar(5))
insert @x values ('AAAA')
insert @x values ('BBBB')
insert @x values ('CCCC')
SELECT
STUFF(
(
SELECT ','+x
FROM @x
FOR XML PATH('')
), 1, 1, ''
) AS ColName
OUTPUT:
ColName
-------------------
AAAA,BBBB,CCCC
(1 row(s) affected)
or like this:
declare @x table (RowID int, x varchar(5))
insert @x values (1,'AAAA')
insert @x values (1,'BBBB')
insert @x values (1,'CCCC')
insert @x values (2,'aa')
insert @x values (3,'abc')
insert @x values (3,'123')
SELECT
a.RowID,
STUFF(
(
SELECT ', '+b.x
FROM @x b
WHERE a.RowID=b.RowID
order by b.x
FOR XML PATH('')
), 1, 2, ''
) AS ColName
FROM @x a
GROUP BY a.RowID
OUTPUT:
RowID ColName
----------- -----------------
1 AAAA, BBBB, CCCC
2 aa
3 123, abc
(3 row(s) affected)
The following TSQL will do the trick, you just need to substitute the appropriate column and table names
DECLARE @vString NVARCHAR(500)
SET @vString = ''
SELECT @vString = @vString + ColumnNameToConcatenate + ','
FROM TableToPickColumnFrom
SELECT SUBSTRING(@vString, 0, LEN(@vString) -1) AS ConcatenatedText
It also remove the trailing ,
from the end of the string so you get:
Value1,Value2,Value3
rathe than:
Value1,Value2,Value3,
精彩评论