March 4, 2011 at 6:03 am
Another one CTE solution:
with
fruit ([value], )as (
select cast('Apple' as nvarchar(max)) , 101 union all
select 'Banana' , 102 union all
select 'Orange' , 103 union all
select 'Melon' , 104 union all
select 'Grape' , 105
),
list as
(
select , [value]
from fruit
where = 101
union all
select f1., list.[value] + ', ' + f1.[value]
from fruit f1
inner join list on f1. = list. + 1
)
select top 1 list.[value]
from list
order by list. desc
March 4, 2011 at 6:22 am
what about this query->select Name+',' from Fruit Order By Id for xml path('')
March 4, 2011 at 7:03 am
What I have never been able to figure out is why the SQL team has never implemented a standard built in aggregate function called ConcatenateToCSV or something.
March 4, 2011 at 7:06 am
Here is alternative and the performance should be a little better as well:
Declare @locBclCodeList Varchar(Max)
Select @locBclCodeList = COALESCE(LTrim(RTrim(@locBclCodeList)) + ',' ,'') + [BclCode]
From dbo.BclCodes
Print ' @locBclCodeList [' + @locBclCodeList + ']'
will creat a comma delimited list from a table of the BclCodes from each record in dbo.BclCodes.
dbo.BclCodes contains:
BclCodeId Int.
BclCOde Varchar(10),
MainBclCode Varchar(10),
PrService Bit,
HrService Bit,
TcService Bit,
ElecRept Bit,
Several other Bit fields.
I used this code to create a comma delimited list that I can use to select clients from other tables:
Where (@locBclCodeList = '*' Or
tblEarnings.BclCode In (Select Word
From dbo.udfGetWord(@locBclCodeList, ',')
Where Word = tblEarnings.BclCode))
dbo.udfGetWord is a udf that will quickly search a delimit list of values.
-- =========================================================================
--Source:udfGetWord
--Version:1.0.0
--Author:R Haverty
--Date:08/30/2008
--Revised:
--
--Function:This udf was copied from GetAllWords2 and modified to return
--the Word Trimmed and in Upper Case.
-----------------------------------------------------------------------------
-- =========================================================================
Use TARS
GO
-- =========================================================================
IF OBJECT_ID ('dbo.udfGetWord') IS NOT NULL
DROP Function dbo.udfGetWord
GO
-- ==================================================================== =======
--Object: UserDefinedFunction [dbo].[GETALLWORDS2]
--Script Date: 08/30/2008 14:10:22
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Author: Igor Nikiforov, Montreal, EMail: udfunctions@gmail.com
--GETALLWORDS2() User-Defined Function Inserts the words from a string into
--the table.
--GETALLWORDS2(@cString[, @cStringSplitting])
--Parameters
--*)@cString nvarchar(4000)- Specifies the string whose words will be
--inserted into the table @GETALLWORDS2.
--*)@cStringSplitting nvarchar(256) - Optional. Specifies the string used to
--separate words in @cString.
--The default delimiter is space.
--Note that GETALLWORDS2( ) uses @cStringSplitting as a single delimiter.
--Return Value table
--Remarks GETALLWORDS2() by default assumes that words are delimited by space.
--If you specify another string as delimiter, this function ignores spaces and
--uses only the specified string.
--
-- Example
--*)Declare @cString nvarchar(4000), @nIndex smallint
--Select @cString = 'We hold these truths to be self-evident,
--that all men are created equal, that they are
--endowed by their Creator with certain unalienable Rights,
--that among these are Life, Liberty and the pursuit of
--Happiness.', @nIndex = 30
--Select WORD from dbo.GETALLWORDS2(@cString, default) where WORDNUM = @nIndex -- Displays 'Liberty'
--Select top 1 WORDNUM from dbo.GETALLWORDS2(@cString, default) order by WORDNUM desc -- Displays 35
--See Also GETWORDNUM() , GETWORDCOUNT() , GETALLWORDS() User-Defined Functions
--If no break string is specified, the function uses space to delimit words.
CREATE function [dbo].[udfGetWord]
(
@cStringnvarchar(4000),
@cStringSplittingnvarchar(256) = ' '
)
Returns @tblGetAllWords table
(
WORDNUMsmallint,
WORDnvarchar(4000),
STARTOFWORDsmallint,
LENGTHOFWORDsmallint
)
--===========================================================================
Begin--udfGetWord
--=========================================================================
Declare @ksmallint,
@BegOfWordsmallint,
@wordcountsmallint,
@nEndStringsmallint,
@nLenSrtingSplittingsmallint,
@flagbit
--=========================================================================
Select@cStringSplitting = isnull(@cStringSplitting, space(1)) ,
@cString = isnull(@cString, '') ,
@BegOfWord = 1,
@wordcount = 1,
@k = 0 ,
@flag = 0,
@nEndString = 1 + datalength(@cString) / (case SQL_VARIANT_PROPERTY(@cString,'BaseType')
When'nvarchar' then 2 else 1 end),
@nLenSrtingSplitting = datalength(@cStringSplitting) /(case SQL_VARIANT_PROPERTY(@cStringSplitting,'BaseType')
When'nvarchar' then 2 else 1 end) -- for unicode
--=========================================================================
While 1 > 0
Begin--while 1 > 0
If @k - @BegOfWord > 0
Begin--If @k - @BegOfWord > 0
Insert into @tblGetAllWords(WORDNUM,
Word,
STARTOFWORD,
LENGTHOFWORD)
Values(@wordcount,
Upper(LTrim(RTrim(substring(@cString,
@BegOfWord ,
@k - @BegOfWord)))),
@BegOfWord,
@k - @BegOfWord)
Select@wordcount = @wordcount + 1, @BegOfWord = @k
End--If @k - @BegOfWord > 0
If @flag = 1
Break
While Charindex( Substring(@cString, @BegOfWord, @nLenSrtingSplitting)
COLLATE Latin1_General_BIN, @cStringSplitting COLLATE Latin1_General_BIN) > 0 -- skip break strings, if any
Set @BegOfWord = @BegOfWord + @nLenSrtingSplitting
select@k = Charindex(@cStringSplitting COLLATE Latin1_General_BIN,
@cString COLLATE Latin1_General_BIN,
@BegOfWord)
If @k = 0
select @k = @nEndString, @flag = 1
End--while 1 > 0
--===========================================================================
Return
End--udfGetWord
March 4, 2011 at 7:13 am
A CTE way of doing it
-- Create a variable as table
DECLARE @Fruit TABLE (
ID INT,
Name varchar(25)
)
-- Populate the table with sample data
INSERT INTO @Fruit(ID, Name)
VALUES
(101,'Apple')
,(102,'Banana')
,(103,'Orange')
,(104,'Melon')
,(105,'Grape')
-- Lets us create two cte's
;WITH fruit(row, Name)
-- fruit is just a sequencially numbered list of names
-- When using ROW_NUMBER we eliminate IDs out of sequence
AS
(
-- ROW_NUMBER() gives us a sequencial list
-- OVER (ORDER BY (SELECT NULL)) order by NULL, low cost order by
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CAST(Name as varchar(max)) FROM @fruit
), fruits(row, String)
AS (
-- Get the first row in the numbered list of names
SELECT row, Name FROM fruit
-- Get the lowest rownumber
WHERE row = (SELECT MIN(row) FROM fruit)
-- This is the magic
UNION ALL
-- Let us begin being recursive
SELECT
-- Fetched rownumber
f2.row,
-- Concatenate previous row with fetched row
f1.String + ', ' + f2.Name
-- This cte
FROM fruits f1
-- Previous cte, the numbered list of names
INNER JOIN fruit f2
-- Next row
ON f1.row + 1 = f2.row
)
-- Ready to return data
SELECT String FROM fruits
-- To get the last row
WHERE row = (SELECT MAX(row) FROM fruits)
What IF we had CONCAT as an aggregate function...
But hey, we can.... SQL CLR is the way to go...
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToOrder = false,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
MaxByteSize = -1
)]
public struct concat :IBinarySerialize
{
private StringBuilder _accumulator;
private string _delimiter;
public Boolean IsNull { get; private set; }
public void Init()
{
_accumulator = new StringBuilder();
_delimiter = string.Empty;
this.IsNull = true;
}
public void Accumulate(SqlString Value, SqlString Delimiter)
{
if (!Delimiter.IsNull
& Delimiter.Value.Length > 0)
{
_delimiter = Delimiter.Value;
if (_accumulator.Length > 0) _accumulator.Append(Delimiter.Value);
}
_accumulator.Append(Value.Value);
if (Value.IsNull == false) this.IsNull = false;
}
public void Merge(concat Group)
{
if (_accumulator.Length > 0
& Group._accumulator.Length > 0) _accumulator.Append(_delimiter);
_accumulator.Append(Group._accumulator.ToString());
}
public SqlString Terminate()
{
// Put your code here
return new SqlString(_accumulator.ToString());
}
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
_delimiter = r.ReadString();
_accumulator = new StringBuilder(r.ReadString());
if (_accumulator.Length != 0) this.IsNull = false;
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
w.Write(_delimiter);
w.Write(_accumulator.ToString());
}
}
But I'm sorry for not reading through the thread... All above already said...
Besides the little trickster in the cte I did with ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
The xml-way is the fastest performer compared to SQL CLR, about ten times faster on a table of 75000 words, and about 500 times faster than the cte-method, and some 1000 times faster than the variable-method
March 4, 2011 at 7:15 am
It is a shame that TSQL lacks the CONNECT BY clause that is available with Oracle. All these complex solutions could be eliminated for many classes of row-joining queries.
March 4, 2011 at 7:15 am
SQL2005 and higher have the coalesce command.
declare @remaining_string varchar(max)
set @remaining_string = ''
select @remaining_string = coalesce (@remaining_string + db.name + char(44), '')
from database db (nolock)
where db.id = X
order by db.name
At the end @remaining_string = 'name1,name2,name3,...,nameN'
March 4, 2011 at 10:52 am
Robert Bourdeau (3/4/2011)
It is a shame that TSQL lacks the CONNECT BY clause that is available with Oracle. All these complex solutions could be eliminated for many classes of row-joining queries.
I'll second that. I've been using the CONNECT BY for over 20 years. Can't live without it. Consequently, I have to re-invent it in some shape, matter, or form in SQL Server.
March 4, 2011 at 10:58 am
I think this (using a cursor to iterate on the list) is the most reasonable solution for the newbie sql dveloper.
Is there a performance issue in this way ?
I think it shuold beat the recursion any time, and I'm not sure about the other one (xml path)
Any insights ?
March 4, 2011 at 11:36 am
My solution where I posted the coalesce solution will eat up a cursor on performance. Another better solution than using a cursor is:
Declare @tblName Table
(
tblNameId Int Identity(1,1),
BclCode Varchar(10),
)
Declare @locMaxtblNameId Int,
@locBclCode Varchar(10),
@locRecdNo Int
-- Get all the Client Numbers
Insert Into @tblName
Select BclCode
From dbo.Client_Main
Order By BclCode
Set @locMaxtblNameId = @@Identity
Set @locRecNo = 1
While tblNameId <= @locMaxtblNameId
Begin
Select @locBclCode = BclCode
From @tblName
Where tblNameId = @locRecNo
Print 'BclCode selected was [' + @locBclCde & ']'
-- Reduce the table size if you don't need the records again
Delete @tblName
Where tblNameId = @locRecNo
Set @locRecNo = @locRecNo + 1
End
-- This works even better in a stored proc where you can create an index for
-- tblNamId and will eat a cursor up in performance
March 4, 2011 at 1:21 pm
Sorry Guys, as I remember the CTE recursion works with depth <= 70
March 4, 2011 at 3:17 pm
Hi guys,
why dont we keep it simple?
CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)
INSERT INTO #test ( id, name )
VALUES ( 101, 'Apple' ),
( 102, 'Banana' ),
( 103, 'Orange' ),
( 104, 'Melon' ),
( 105, 'Grape' )
SELECT * FROM #test
DECLARE @fruit VARCHAR(MAX)
SELECT @fruit = COALESCE(@fruit + '', '') + name
FROM #test
SELECT Colors = @fruit
It take no efford at all.
Artur
March 4, 2011 at 5:26 pm
Just curious as to why you didn't consider the built-in T-SQL function called COALESCE. I often need to do what you describe and have found that COALESCE is relatively simple code and performs well.
I use a function similar to the following:
CREATE FUNCTION dbo.udf_Get_FG_Warehouse_List (@DocumentID int)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Warehouse_List varchar(1000)
SELECT
@Warehouse_List = COALESCE(@Warehouse_List + ', ', '') + a.Name
FROM
Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.ID
WHERE
b.NPFID = @DocumentID
ORDER BY
a.Name
RETURN
@Warehouse_List
END
March 4, 2011 at 9:07 pm
arturv (3/4/2011)
Hi guys,why dont we keep it simple?
CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)
INSERT INTO #test ( id, name )
VALUES ( 101, 'Apple' ),
( 102, 'Banana' ),
( 103, 'Orange' ),
( 104, 'Melon' ),
( 105, 'Grape' )
SELECT * FROM #test
DECLARE @fruit VARCHAR(MAX)
SELECT @fruit = COALESCE(@fruit + '', '') + name
FROM #test
SELECT Colors = @fruit
It take no efford at all.
Artur
This is what I was thinking, and it what was several others already mentioned. Here is a "WITH" version of that one from Artur:
DECLARE @joined VARCHAR(max);
WITH fruit as (
select 'Apple' as name, 101 as id union all
select 'Banana' as name, 102 as id union all
select 'Orange' as name, 103 as id union all
select 'Melon' as name, 104 as id union all
select 'Grape' as name, 105 as id
)
SELECT @joined=COALESCE(@joined+',', '')+name FROM fruit
SELECT @joined
March 5, 2011 at 9:49 am
peter.stanford 73369 (3/4/2011)
Just curious as to why you didn't consider the built-in T-SQL function called COALESCE. I often need to do what you describe and have found that COALESCE is relatively simple code and performs well.I use a function similar to the following:
CREATE FUNCTION dbo.udf_Get_FG_Warehouse_List (@DocumentID int)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Warehouse_List varchar(1000)
SELECT
@Warehouse_List = COALESCE(@Warehouse_List + ', ', '') + a.Name
FROM
Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.ID
WHERE
b.NPFID = @DocumentID
ORDER BY
a.Name
RETURN
@Warehouse_List
END
That works fine but is a form of RBAR because the function is necessarily a scalar function which refers to a table. That can make performance actually worse than a cursor.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 121 through 135 (of 159 total)
You must be logged in to reply to this topic. Login to reply