• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 50
  • Last Modified:

Convert Multiple rows based on Filename and RowID into 1 row with multiple columns.

Hello Experts,

I am really struggling this SQL query for a week now. My query did not output the right results. I tried pivot, tsql but no luck.

I have a SQL table with multiple rows and columns. All I want is convert all rows based on filename and RowID into one row with multiple columns. Whatever the RowID is the Column Name per filename. Please see attached file for more info.

Please help.

Thank you again.
Convert_MultipleRowswithSamefilenam.xlsx
0
Queennie L
Asked:
Queennie L
  • 10
  • 5
  • 5
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Queenie,

One of the challenges here is that you generally have to know how many columns in the final result (or the maximum number and code to that value).  If you can live with a single column (plus any control columns that you want or need) it's pretty easy with recursive SQL.  If the RowID values are consecutive for each value of Filename, it's almost trivial.

WITH rq (FileName, Text)
AS
(
  SELECT Filename, RowID, Column0 || ',' ||Column1 as Text
  FROM mytable WHERE RowID = 1

  UNION ALL

  SELECT Filename, t1.RowID, t0.Text || ',' ||Column0 || ',' || Column1
  FROM rq t0, mytable t1
  WHERE t0.Filename = t1.Filename
    AND t0.RowID + 1 = t1.RowID
)
SELECT Filename, Text
FROM rq t2
WHERE t2.RowID = (SELECT max (RowID) FROM rq t3 WHERE t2.Filename = t3.Filename)

Open in new window


Note that in all the civilized world :) the double pipe is the concatenation operator.  SQL Server generally uses the plus sign (+) so replace the double-pipe with plus in that query.

There's a full write-up on how this recursive query work here:

  https://www.experts-exchange.com/articles/3618/Recursive-SQL-in-DB2-Converting-rows-to-columns.html


Good Luck!
Kent
0
 
Queennie LAuthor Commented:
@Kent Olsen:

I have an error:

'rq' has more columns than were specified in the column list.

Thank you again.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Oops.  :)

Line 1 should be:

  WITH rq (FileName, RowID, Text)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Queennie LAuthor Commented:
@Kent Olsen:

I still have an error:

'rq' has more columns than were specified in the column list.

Thank you again.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
I don't see what would cause that error with the edits I suggest.  Note that if either Column0 or Column1 are really numeric, they'll need to be recast to a varchar type as SQL Server will try to recast on the fly and throw an error .


WITH rq (FileName, RowID, Text)
AS
(
  SELECT Filename, RowID, Column0 + ',' + Column1 as Text
  FROM mytable WHERE RowID = 1

  UNION ALL

  SELECT Filename, t1.RowID, t0.Text + ',' + Column0 + ',' + Column1
  FROM rq t0, mytable t1
  WHERE t0.Filename = t1.Filename
    AND t0.RowID + 1 = t1.RowID
)
SELECT Filename, Text
FROM rq t2
WHERE t2.RowID = (SELECT max (RowID) FROM rq t3 WHERE t2.Filename = t3.Filename)

Open in new window

0
 
Queennie LAuthor Commented:
I know this dynamic SQL query is working but it only show [Column 0].


declare @col varchar(1000)
declare @sql varchar(2000)
select @col = COALESCE(@col + ', ','') + QUOTENAME([RowID])
from #Conversion Group by RowID
Set @sql='select * from (select [Filename],RowID,[Column 0] from #Conversion) src 
PIVOT (Max([Column 0]) FOR RowID
IN ('+@col+')) pvt'
EXEC(@sql)

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Queenie,

I created a test scenario of your data and my sql.  There were 2 items in the SQL that had to be corrected.  As I feared, SQL Server was trying to automatically cast Text into an integer, but it was throwing a different error than you showed.  The corrected SQL below works in my environment.

WITH rq (FileName, RowID, Text)
AS
(
  SELECT Filename, RowID, cast (Column0 + ',' + Column1 as varchar) as Text
  FROM mytable WHERE RowID = 1

  UNION ALL

  SELECT t0.Filename, t1.RowID, cast (t0.Text + ',' + Column0 + ',' + Column1 as varchar)
  FROM rq t0, mytablex t1
  WHERE t0.Filename = t1.Filename
    AND t0.RowID + 1 = t1.RowID
)
SELECT Filename, Text
FROM rq t2
WHERE t2.RowID = (SELECT max (RowID) FROM rq t3 WHERE t2.Filename = t3.Filename)

Open in new window

0
 
Queennie LAuthor Commented:
@Kent Olsen:

Text field should not be concatenated. It should be in separate columns.

Thank you again.
0
 
Queennie LAuthor Commented:
Any help?

Thanks.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Queenie,

As I said at the start, if you you can live with a single column for the result, recursive SQL will do just fine.  If you're looking to use the result as a source to Excel it'll work well.  But if you need separate columns for each item, you have to make some determinations and set some limits from the start.  Plus, the SQL gets quite wordy and ugly.

From the article in the link posted above:

SELECT t0.word ||
  COALESCE (' ' || t1.word, '') ||
  COALESCE (' ' || t2.word, '') ||
  COALESCE (' ' || t3.word, '') ||
  COALESCE (' ' || t4.word, '') ||
  COALESCE (' ' || t5.word, '') ||
  COALESCE (' ' || t6.word, '')
FROM rec t0
LEFT JOIN rec t1
  ON t0.snum = t1.snum
 AND t1.wordnum = 2
LEFT JOIN rec t2
  ON t0.snum = t2.snum
 AND t2.wordnum = 3
LEFT JOIN rec t3
  ON t0.snum = t3.snum
 AND t3.wordnum = 4
LEFT JOIN rec t4
  ON t0.snum = t4.snum
 AND t4.wordnum = 5
LEFT JOIN rec t5
  ON t0.snum = t5.snum
 AND t5.wordnum = 6
LEFT JOIN rec t6
  ON t0.snum = t6.snum
 AND t6.wordnum = 7
WHERE t0.wordnum = 1;

Open in new window


It's ugly, and from a technical standpoint it's incomplete as it will only report the first 7 words of a sentence.  Note that a couple more joins were done than necessary to produce the sample sentence to demonstrate that the query must contain at least enough joins to rebuild the sentence and that the query must gracefully handle the NULL values that are generated when the join is meaningless.

Your sample data has 36 and 62 rows for the two Filename groups.  That's 35 and 61 joins respectively.  And if you have other data with more rows, the number of joins will grow, too.

The only real choices are 1)  Really long and ugly SQL to join all the rows;  2)  Recursive SQL and the single column result;  and 3) Pivot the results.  

You've already got a PIVOT query written.  Maybe we should proceed with it?
0
 
Mark WillsTopic AdvisorCommented:
Little bit involved and had to make it unpivot so we can then pivot...
declare @sql varchar(max)
declare @col varchar(max)
select @col = isnull(@col + ', ','') + QUOTENAME(colnames)
from (select uniqueid,rowid, [filename], colvalues, replace(colnames,' ','_'+format(rowid,'00')+'_') colnames
      from #conversion
      unpivot (colvalues for colnames in ([Column 0], [Column 1])) upv) s
group by colnames

print @col

Set @sql='select * from (select [filename], colvalues, replace(colnames,'' '',''_''+format(rowid,''00'')+''_'') colnames
      from #conversion
      unpivot (colvalues for colnames in ([Column 0], [Column 1])) upv) src 
PIVOT (Max([colvalues]) FOR colnames
IN ('+@col+')) pvt'

print @sql

EXEC(@sql)

Open in new window

Let me know your thoughts....
0
 
Queennie LAuthor Commented:
@MarkWillis:

I will test it and will let you know.

Thank you, thank you again.
0
 
Queennie LAuthor Commented:
@MarkWillis:

When I run it, there is an error:

"The type of column "Column 1" conflicts with the type of other columns specified in the UNPIVOT list."

Thank you again.
0
 
Mark WillsTopic AdvisorCommented:
What datatypes are [column 0] and [column 1] ?

When I imported the spreadsheet they appeared to be varchar.

We can always cast as varchar to make sure :)
0
 
Mark WillsTopic AdvisorCommented:
e.g.
declare @sql varchar(max)
declare @col varchar(max)
select @col = isnull(@col + ', ','') + QUOTENAME(colnames)
from (select rowid, [filename], colvalues, replace(colnames,' ','_'+format(rowid,'00')+'_') colnames
      from  (select rowid,[filename],cast([column 0] as varchar(30)) [column 0], cast([column 1] as varchar(30)) [column 1] from #conversion) sq
      unpivot (colvalues for colnames in ([Column 0], [Column 1])) upv) s
group by colnames

print @col

Set @sql='select * from (select [filename], colvalues, replace(colnames,'' '',''_''+format(rowid,''00'')+''_'') colnames
      from (select rowid,[filename],cast([column 0] as varchar(30)) [column 0], cast([column 1] as varchar(30)) [column 1] from #conversion) sq
      unpivot (colvalues for colnames in ([Column 0], [Column 1])) upv) src 
PIVOT (Max([colvalues]) FOR colnames
IN ('+@col+')) pvt'

print @sql

EXEC(@sql)

Open in new window

0
 
Queennie LAuthor Commented:
@MarkWillis:

They are nvarchar(150).

OMG, WOW! It seems like it is working. I tested it.

I don't know what to say but a big "THANK YOU". You really don't know how you helped me.

I really appreciate it from the bottom of my heart.

Thank you again.
0
 
Mark WillsTopic AdvisorCommented:
Slightly different approach - wasnt happy with colnames and unpivot can be achieved just as easily by a union into a temp table....

Should be a little bit more straightforward and easier to understand / maintain.

if object_id('tempdb..#conversion_pvt','U') is not null drop table #conversion_pvt

select * into #conversion_pvt from
(
  Select uniqueid, format(rowid,'000')+'0' [RowID], [filename], cast([column 0] as varchar(50)) [Colvalue]
  from #conversion
  union all
  Select uniqueid, format(rowid,'000')+'1' [RowID], [filename], cast([column 1] as varchar(50)) [Colvalue]
  from #conversion
) src
order by 1,2,3


declare @sql varchar(max)
declare @col varchar(max)
select @col = isnull(@col + ', ','') + '['+[RowID]+']' from #Conversion_pvt Group by RowID

print @col

Set @sql='select * from (select [Filename],[RowID],[Colvalue] from #Conversion_pvt) src 
          PIVOT (Max([colvalue]) FOR RowID IN ('+@col+')) pvt'

print @sql

EXEC(@sql)

Open in new window

1
 
Queennie LAuthor Commented:
@MarkWillis:

Wow! It is working. I just tested it. Can I changed the column header to [Column 0] to last [Column ]?

How to insert this output results to a new  SQL table?

Thank you again.
0
 
Mark WillsTopic AdvisorCommented:
>> Can I changed the column header to [Column 0] to last [Column ]?

*laughing* If I knew what you are asking, I am pretty sure the answer would be 'Of Course'.

As for outputting results to a table, then yes, I would be inclined to do a similar thing as above - select * into <table name> from <datasource>

Even if that is a temp table then put away into a more appropriate permanent table.
0
 
Queennie LAuthor Commented:
Thank you Mark for your help and suggestion.

Also, thank you for those who participated to  help me.

I really appreciate from the bottom of my heart.
0

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now