Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get concatenated value for a column without cursor Expand / Collapse
Author
Message
Posted Monday, July 05, 2010 9:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 11:22 PM
Points: 2,018, Visits: 65
Comments posted to this topic are about the item Get concatenated value for a column without cursor
Post #947687
Posted Tuesday, July 06, 2010 2:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:47 AM
Points: 1,174, Visits: 2,635
Here's a slightly more succinct version inspired by code from this article http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

SELECT t1.col1,
(
SELECT
CASE WHEN ROW_NUMBER() OVER(ORDER BY col1, col2)=1 THEN '' ELSE ',' END + col2
FROM
TestTable
WHERE
col1 = t1.col1
ORDER BY
col1 ,col2
FOR XML PATH('')
)
FROM
TestTable t1
GROUP BY
col1



--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #947761
Posted Tuesday, July 06, 2010 2:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 1:11 AM
Points: 19, Visits: 40
Your code is not working. It 's giving error ColRowNo is invalid Column. Can you please try to remove that error from your code. I executed that code in sql server 2008
Post #947763
Posted Tuesday, July 06, 2010 3:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 11:22 PM
Points: 2,018, Visits: 65
Please check if following statement is missing from your script -

Alter Table TestTable Add ColRowNo int

if it is missing, then add it next to statement "select * from TestTable"
it will work.
Post #947778
Posted Tuesday, July 06, 2010 7:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 9:12 AM
Points: 27, Visits: 185
I'm getting a different error:
Update TestTable 
Set ColRowNo = RowNo
From
(
    Select RowID, Row_Number() Over (Partition By Col1 Order by RowID) RowNo From TestTable
) B Where TestTable.RowID = B.RowID
;

gives this:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

Post #947911
Posted Tuesday, July 06, 2010 9:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:47 AM
Points: 273, Visits: 441
Tim, I had the same problem. I just removed all the offending whitespace and it works fine now:

---------------------------------------------------------------------
-- Create Table and Insert data
---------------------------------------------------------------------
if object_id('tempdb..TestTable')is not null drop table TestTable
Create Table TestTable (RowID Int Identity(1, 1), Col1 int, Col2 char(3))

Insert TestTable Values (1, 'ABC')
Insert TestTable Values (1, 'DEF')
Insert TestTable Values (1, 'GHI')
Insert TestTable Values (2, 'JKL')
Insert TestTable Values (2, 'MNO')
Insert TestTable Values (3, 'PQR')
Insert TestTable Values (3, 'STU')

select * from TestTable
---------------------------------------------------------------------
-- Add TestTable column and Insert Row Number
---------------------------------------------------------------------
Alter Table TestTable Add ColRowNo int

Update TestTable
Set ColRowNo = RowNo
From
(Select RowID
, Row_Number() Over (Partition By Col1 Order by RowID) RowNo
From TestTable
)as B Where TestTable.RowID = B.RowID
;
---------------------------------------------------------------------
-- Concatenate Col2 in comma seperated form Group by Col1
---------------------------------------------------------------------
With CTE (X, Y, Z) AS
(Select Col1, Convert(Varchar,Col2), ColRowNo From TestTable Where ColRowNo = 1
Union All
Select Col1, Convert(Varchar, Y + ',' + Convert(Varchar, Col2)) Y, ColRowNo
From TestTable
Inner Join CTE on Col1 = X and ColRowNo = Z + 1
)

Select X Col1, Y [Concatenated Col2] From CTE
Inner Join
(Select Col1, Max(ColRowNo) ColRowNo From TestTable Group by Col1
) TestTable on X = Col1 and Z = ColRowNo
Order by X
OPTION (MAXRECURSION 2);

Drop Table TestTable
GO

Post #947965
Posted Tuesday, July 06, 2010 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 03, 2010 12:18 PM
Points: 32, Visits: 122

Virtuoso coding, certainly, but why not take advantage of SQL Server's ability to create custom aggregation functions? Microsoft's example can be found here:

http://msdn.microsoft.com/en-us/library/ms182741.aspx
Post #947970
Posted Tuesday, July 06, 2010 1:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 190, Visits: 878
Mine isn't nearly as fancy, and it sticks a dangling comma on the end, but when I just want a quick listing of all columns in a table (say, for a select statement where they are in Ordinal Position order), I use the following (just change the <tablenamehere> to a real table name):



DECLARE @colline varchar(4000)
SET @colline = ''


SELECT @colline=@colline+COLUMN_NAME + ',' FROM information_schema.columns
WHERE TABLE_NAME = '<tablenamehere>'
ORDER BY ORDINAL_POSITION

SELECT @colline
Post #948156
Posted Tuesday, July 06, 2010 10:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 11:22 PM
Points: 2,018, Visits: 65
It is not mainly for comma seperated column name. It can be use when you want comma seperated data on grouping of some other field. Please see the output resultset 1 and resultset 2.
Post #948287
Posted Wednesday, July 07, 2010 1:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 9:12 AM
Points: 27, Visits: 185
Thanks Wesley,

That certainly makes it easier to parse

Post #948342
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse