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
Queennie LAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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 OlsenDBACommented:
Oops.  :)

Line 1 should be:

  WITH rq (FileName, RowID, Text)
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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 OlsenDBACommented:
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 OlsenDBACommented:
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 OlsenDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.