Convert single column with multiple rows into a single row having multiple columns

Question

I need to convert a column data into single row with multiple columns

Example - I created a temp table to load data for the column

CREATE TABLE TestC (Comments Char(100), Row_Count [int] IDENTITY(1,1))

INSERT INTO TestC VALUES('A'),('B'),('C'),('D')

Select Min( Case When Row_Count = 1 Then Comments End ) As Comments 
, Min( Case When Row_Count = 2 Then Comments End ) As Comments 
, Min( Case When Row_Count = 3 Then Comments End ) As Comments 
, Min( Case When Row_Count = 4 Then Comments End ) As Comments 
, Min( Case When Row_Count = 5 Then Comments End ) As Comments 
, Min( Case When Row_Count = 6 Then Comments End ) AS Comments 
, Min( Case When Row_Count = 7 Then Comments End ) AS Comments 
FROM TestC

Result

I am trying to make it dynamic query, Below is the code snippet I am trying out currently

DECLARE @sql AS NVARCHAR (MAX);
SELECT   @sql =  ' SELECT Min (CASE WHEN [Row_Count] =' + CAST ([Row_Count] AS CHAR(5)) + 
' THEN [Comments] END) AS Comments'
FROM     [dbo].[TestC];
SET @sql = @sql + N' FROM  [dbo].[TestC] ';
PRINT @sql
EXECUTE sp_executesql @sql;

This still needs some tweaking. Appreciate your Help..

If i get it right you want to convert rows into columns, then why not simply crosstab it..!! — Anuj Patel
– Anuj Patel, Commented Jun 9, 2012 at 10:00
Yes. You just need to convert from rows to columns. Let me try cross tab — Siva
– Siva, Commented Jun 9, 2012 at 17:07

Community · Accepted Answer · 2017-05-23 12:19:42Z

1

You can use pivot query such as below :

SELECT Pvt.*
FROM (
    SELECT *
    FROM TestC
    )AS P
PIVOT (MIN(Comments) FOR Row_Count IN ([1],[2],[3],[4],[5],[6],[7]))Pvt

EDIT : You can see below links:

Dynamic Pivot Link 1

Dynamic Pivot Link 2

Dynamic Pivot Link 3

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Jun 9, 2012 at 10:42

mehdi lotfi

11.6k18 gold badges85 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siva Over a year ago

This need to be dynamic actually.Here in the answer it is hardcoded

Collectives™ on Stack Overflow

Convert single column with multiple rows into a single row having multiple columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related