asked on

Convert multiple rows into One row multiple columns based on the rownumber

I have a SQL table.

All I need to do is to convert rows into into one based on rownumber and column1.

My code is not working.

Here's my code:

SELECT [Filename]
      ,[ImportDate]
      ,[DataName] 
,MAX(CASE WHEN [RowNumber] = 1 THEN [Column1] ELSE NULL END) AS [1_COL1]
,MAX(CASE WHEN [RowNumber] = 2 THEN [Column1] ELSE NULL END) AS [2_COL1]
,MAX(CASE WHEN [RowNumber] = 1 THEN [Column2] ELSE NULL END) AS [1_COL2]
,MAX(CASE WHEN [RowNumber] = 2 THEN [Column2] ELSE NULL END) AS [2_COL2]
,MAX(CASE WHEN [RowNumber] = 1 THEN [Column3] ELSE NULL END) AS [1_COL3]

,MAX(CASE WHEN [RowNumber] = 1 THEN [Column4] ELSE NULL END) AS [1_COL4]
,MAX(CASE WHEN [RowNumber] = 1 THEN [Column5] ELSE NULL END) AS [1_COL5]
,MAX(CASE WHEN [RowNumber] = 2 THEN [Column3] ELSE NULL END) AS [2_COL3]
,MAX(CASE WHEN [RowNumber] = 2 THEN [Column4] ELSE NULL END) AS [2_COL4]

,MAX(CASE WHEN [RowNumber] = 2 THEN [Column5] ELSE NULL END) AS [2_COL5]





FROM (SELECT a.[Column0]
      ,a.[Column1]
      ,a.[Column2]
      ,a.[Column3]
      ,a.[Column4]
      ,a.[Column5]
      ,a.[Column6]
      ,a.[Column7]
      ,a.[Column8]
      ,a.[Column9]
      ,a.[Filename]
      ,a.[ImportDate]
      ,a.[DataName]
      ,a.[RowNumber] 
      FROM [dbo].[Test] a 
     ) crossTab 
GROUP BY [Filename]
      ,[ImportDate]
      ,[DataName]

Open in new window

Please see attached.

Please help.

Thank you very much.
SQL-table-and-Output.xlsx

Mark Wills

That looks do-able. Think the MAX rownumber might need some more thought. Seems the same column gives results depending on rownumber = 1 or 2 - that might need to be resolved first (maybe).

Let me have a closer look at your attachments and I'll get back to you soon...

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Queennie L

ASKER

@Mark Willis:

Yes it makes sense.

I will run this and test and I will let you know.

Thank and thank you again.

Queennie L

ASKER

@Mark Willis:

Thank and Thank you again and again.

I have been working with this query for 3 days.

It is working.

I appreciate your help from the bottom of my heart.

Thank you.

Queennie L

ASKER

@Mark Willis:

Thank and Thank you again and again.

I have been working with this query for 3 days.

It is working.

I appreciate your help from the bottom of my heart.

Thank you.

Mark Wills

It is my pleasure to have been able to help. Enjoyed the challenge.

Thank you very much for sharing your problem and allowing us to assist you in finding a solution.

Kind Regards,
Mark Wills