Question: [![](https://i.stack.imgur.com/DUo2y.png)](https://i.stack.imgur.com/DUo2y.png)

I want to transpose this table which has only 1 row with client id and the response columns as response1, response2 and so on. 

I tried with pivot but not able to apply correctly

Answer: 
Try this:

```
IF OBJECT_ID('tempdb..#DataSource') IS NOT NULL
BEGIN
    DROP TABLE #DataSource;
END;

CREATE TABLE #DataSource
(
    [ClientID] INT
   ,[Response] VARCHAR(24)
);

INSERT INTO #DataSource ([ClientID], [Response])
VALUES (3316, 'omellette')
      ,(3316, 'mmer')
      ,(3316, 'joy')
      ,(3316, 'gift')
      ,(3316, 'strong')
      ,(3316, 'rrailores')
      ,(3316, 'something else');

SELECT *
FROM
(
    SELECT [ClientID]
          ,[Response]
          ,'Response' + CAST(ROW_NUMBER() OVER (PARTITION BY [ClientID] ORDER BY (SELECT 1)) AS VARCHAR(12)) AS [Col]
    FROM #DataSource
) DS
PIVOT
(
    MAX([Response]) FOR [Col] IN ([Response1], [Response2], [Response3], [Response4], [Response5], [Response6], [Response7])
) PVT;

```

[![enter image description here](https://i.stack.imgur.com/wqFke.png)](https://i.stack.imgur.com/wqFke.png)

Here is the dynamic version. Note we are first getting unique responses (in case you want to get data for more clients and they share responses, you will get them in one column):

```
DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@DynamicColumns NVARCHAR(MAX);

WITH DataSource AS 
(
    SELECT DISTINCT [Response]
    FROM #DataSource
)
SELECT @DynamicColumns = STUFF
(
    (
        SELECT DISTINCT ',[Response' + CAST(ROW_NUMBER() OVER (ORDER BY [Response]) AS VARCHAR(12)) + ']'
        FROM DataSource
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,1
    ,''
);

SET @DynamicTSQLStatement = N'
SELECT *
FROM
(
    SELECT [ClientID]
          ,[Response]
          ,''Response'' + CAST(ROW_NUMBER() OVER (PARTITION BY [ClientID] ORDER BY [Response]) AS VARCHAR(12)) AS [Col]
    FROM #DataSource
) DS
PIVOT
(
    MAX([Response]) FOR [Col] IN ('+@DynamicColumns+')
) PVT;';

EXEC sp_executesql @DynamicTSQLStatement;

```
Try this : this can be achieve by using `[STUFF](https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql)`

```
SELECT 
  CLientID
, STUFF((
    SELECT N', ' + CAST(Response AS VARCHAR(4000))
    FROM TableName t2
    WHERE t1.CLientID= t2.CLientID
    FOR XML PATH ('')), 1, 2, '') AS Response
FROM TableName t1

```