June 21, 2016 at 4:41 pm
In my sample code, at the end i want @database to be A,B,C. I am not able to concatenate them.
DECLARE @database NVARCHAR(100) ,
@database2 NVARCHAR(100) ,
@database3 NVARCHAR(100)
DECLARE @dbs TABLE ( DBname NVARCHAR(100) )
INSERT INTO @dbs
VALUES ( 'A' ),
( 'B' )
,
( 'C' )
DECLARE databases CURSOR
FOR
SELECT DBname
FROM @dbs
OPEN databases
FETCH NEXT FROM databases INTO @database
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
FETCH NEXT FROM databases INTO @database
END
CLOSE databases
DEALLOCATE databases
June 21, 2016 at 8:37 pm
SELECT
COALESCE(
STUFF(
(SELECT ',' + CAST(dbs AS VARCHAR(10)) AS [text()]
FROM #l
ORDER BY dbs
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),
1, 1, ''),
'') AS letters
FROM #l as c;
June 22, 2016 at 1:13 am
additional information:
SQL Server 2016 offers new feature, STRING_SPLIT() which returns delimited strings as records.
e.g.
SELECT STRING_SPLIT('A,B,C',',')
Output:
A
B
C
June 22, 2016 at 7:19 am
durga.palepu (6/22/2016)
additional information:SQL Server 2016 offers new feature, STRING_SPLIT() which returns delimited strings as records.
e.g.
SELECT STRING_SPLIT('A,B,C',',')
Output:
A
B
C
While that is true it has no relevance in this question. The OP already has each value in a separate row. They are trying to put them together into a single value.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2016 at 7:23 am
I knew that, I've provided additional useful information.
June 22, 2016 at 7:24 am
I clearly mentioned that its additional information.
June 22, 2016 at 8:33 am
May be i didn't explain it correctly. Basically the curson will loop through list of databases and at the end the value in the variable @database should be A, B ,C.
June 22, 2016 at 8:45 am
No, I think you explained it quite well. But you don't need a cursor to do this. Does what santiagoc93 posted not work for you?
John
June 22, 2016 at 9:22 am
Here is the concept posted by santiagoc93 using your sample data.
DECLARE @dbs TABLE ( DBname NVARCHAR(100) )
INSERT INTO @dbs
VALUES ( 'A' ),
( 'B' ),
( 'C' )
SELECT distinct
STUFF((SELECT ',' + CAST(DBname AS VARCHAR(10))
FROM @dbs
ORDER BY DBname
FOR XML PATH(''))
, 1, 1, '') AS DBNames
FROM @dbs
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2016 at 9:39 am
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.
June 22, 2016 at 9:50 am
curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.
Do you know how to use a select statement to populate a variable? It is no different here.
However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.
Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2016 at 12:41 pm
Sean Lange (6/22/2016)
curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.Do you know how to use a select statement to populate a variable? It is no different here.
However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.
Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.
You are right i did horrible job in explaining my question :(.
The above query works but i went with a different solution.
June 22, 2016 at 4:35 pm
curious_sqldba (6/22/2016)
Sean Lange (6/22/2016)
curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.Do you know how to use a select statement to populate a variable? It is no different here.
However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.
Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.
You are right i did horrible job in explaining my question :(.
The above query works but i went with a different solution.
Please post your solution.
June 23, 2016 at 2:36 pm
curious_sqldba (6/22/2016)
Sean Lange (6/22/2016)
curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.Do you know how to use a select statement to populate a variable? It is no different here.
However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.
Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.
You are right i did horrible job in explaining my question :(.
The above query works but i went with a different solution.
Two way street here... what is the solution that you did go for? It might help US in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply