SQL Server Convert Select a Column and Convert It to a String

SQL Server convert select a column and convert it to a string

You can do it like this:

Fiddle demo

declare @results varchar(500)

select @results = coalesce(@results + ',', '') +  convert(varchar(12),col)
from t
order by col

select @results as results

| RESULTS |
-----------
| 1,3,5,9 |

Convert column to string in SQL Select

For concatanating numbers in MSSQL-2005 you should use CAST

CAST(loginsFailed AS VARCHAR(10)) + '/' + CAST(LoginCount AS VARCHAR(10))

loginsFailed and loginCount above is actually your select count distinct fragments

I hope that this works

CAST ((SELECT COUNT(DISTINCT loginsFailed) FROM users WHERE users.employeedID = userDetails.employeeID AND users.startdate = 01-01-2013) AS VARCHAR(10))
+ '/' +
CAST ((SELECT COUNT(DISTINCT logins) FROM users WHERE users.employeedID = userDetails.employeeID AND users.startdate = 01-01-2013) AS VARCHAR(10))

Select all columns but change a column to string

Select the other columns individually, and CAST(xyz AS NVARCHAR)

SELECT foo, bar, CAST(xyz AS NVARCHAR)
FROM myTable

SQL Server : convert column by different condition in select statement

You need to use a Case expression. Assuming the logic above does what you need, this sort of thing should provide the result in the structure you want...

select ta.columna,
       case 
           when CHARINDEX('SHOP',ta.address) > 0 then
               RIGHT(ta.address, len(ta.address) - charindex('SHOP', ta.address)+1)
           when CHARINDEX('LIGHTBOX',ta.address) > 0 then
               RIGHT(ta.address, len(ta.address) - charindex('LIGHTBOX', ta.address)-8)
           when CHARINDEX('ADV',ta.address) > 0 then
               RIGHT(ta.address, charindex('ADV', ta.address)-3)
       end as address
from tablea ta

Convert SQL Server result set into string

Test this:

 DECLARE @result NVARCHAR(MAX)

 SELECT @result = STUFF(
                        (   SELECT ',' + CONVERT(NVARCHAR(20), StudentId) 
                            FROM Student 
                            WHERE condition = abc 
                            FOR xml path('')
                        )
                        , 1
                        , 1
                        , '')

Convert string from column to parameter in dynamic SQL?

While agreeing with the issue of dynamic prone to injection attacks, and assuming some random values for your non initialized variables, here is how I approach

The trick is

SELECT @Condition = REPLACE(@Condition, '@FieldName', @FieldName )
SELECT @Condition = REPLACE(@Condition, '@CustName', @CustName )

So the main Query will be

declare 
    @TableName as nvarchar(10) = 'MyTbl',
    @FieldName as nvarchar(20) = 'FldName',
    @CustName as Nvarchar(50) = 'C1' ,
    @Condition as NVARCHAR(MAX) 
    
SELECT @Condition = (SELECT o.Condition FROM CustomerConditions o WHERE o.[Group] = @CustName)
SELECT @Condition = REPLACE(@Condition, '@FieldName', @FieldName )
SELECT @Condition = REPLACE(@Condition, '@CustName', @CustName )

declare @strSQL as NVARCHAR(MAX)
SET @strSQL = 'DECLARE @FieldName as nvarchar(20),       
@CustName as nvarchar(50)
;WITH NewCTE AS (
                    SELECT Tab1.Group, '+@FieldName+'
                    FROM (
                   SELECT
                    '+@Condition+' 
                    '+@FieldName+'
                    FROM '+@TableName+' as c) as Tab1)
            
SELECT * FROM NewCTE'

Select(@strSQL)

First provide proper values to your variables and execute this.
Instead of executing the query, what I did was create the query to see whether it got created as you want.

To be certain, you can copy paste the outcome and execute that.
Then you can change the last line to Execute ...

In SQL, how to convert a column of a comma separated key string to a comma separated value string

Next time you would need to provide ##1-4.
And learn from this answer what it means, i.e. a minimal reproducible example.
You copy it to SSMS and launch it there.

Here is how to implement it in SQL Server 2016:

• STRING_SPLIT() to break it down, one AssignedTo per row.
• SELECT ... FOR XML ... to revert it back to one row for each task.

SQL

-- DDL and sample data population, start
DECLARE @tblA TABLE (TaskID INT PRIMARY KEY, TaskName VARCHAR(100), AssignedTo VARCHAR(30));
INSERT INTO @tblA (TaskID, TaskName, AssignedTo) VALUES
(1, 'Task 1', '1,4'),
(2, 'Task 2', '3'),
(3, 'Task 3', '2,3'),
(4, 'Task 4', '2,4,5');

DECLARE @tblB TABLE (AssigneeID INT PRIMARY KEY, [Name] VARCHAR(30));
INSERT INTO @tblB (AssigneeID, [Name]) VALUES
(1, 'John Smith'),
(2, 'Janet Wright'),
(3, 'Tom Morgan'),
(4, 'Kevin Warren'),
(5, 'Mike Taylor')
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH cte AS
(
    SELECT * FROM @tblA
        CROSS APPLY (SELECT value FROM STRING_SPLIT(AssignedTo, @separator)) AS x
        INNER JOIN @tblB AS b ON x.value = b.AssigneeID
)
SELECT p.TaskID, p.TaskName
    , STUFF((SELECT DISTINCT
                     CONCAT(@separator, c.Name)
                     FROM cte AS c
                     WHERE c.TaskID = p.TaskID
                     FOR XML PATH ('')),
             1, 1, '') AS NameAssignedTo
FROM cte AS p
GROUP BY p.TaskID, p.TaskName;

Output

+--------+----------+---------------------------------------+
| TaskID | TaskName |            NameAssignedTo             |
+--------+----------+---------------------------------------+
|      1 | Task 1   | John Smith,Kevin Warren               |
|      2 | Task 2   | Tom Morgan                            |
|      3 | Task 3   | Janet Wright,Tom Morgan               |
|      4 | Task 4   | Janet Wright,Kevin Warren,Mike Taylor |
+--------+----------+---------------------------------------+

---

Related Topics