Merge Multiple rows into one row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merge Multiple rows into one row

Author

Topic

threep
Starting Member

4 Posts

Posted - 2010-03-08 : 12:10:40

Hello all, so I'm not really an advanced sql user but one of the problems I've encountered requires an sql statement that is beyond me. I've googled for answers but couldn't understand them and was hoping I could get some help.

Basically the problem I'm facing is that we've upgraded to a newer version of an application with a database backend, but we have other programs that use the database to display information. With the upgrade, the table structure has changed so the program no longer works.

The table for one type of item before the upgrade looked something like this:

ID Voucher ProjectCode Bucket Document_Date
333 34343 2343 7/1/2008 7/14/2008

But now the table structure has changed so that each of these individual columns (Voucher, ProjectCode, Bucket, Document_Date) are in different rows. Also the table that holds these rows does not just hold data for this one type of item, rather every item is held in the table and variables are associated with what a column represents. It makes more sense if I just show the new table structure:

id prop_id str_val num_val date_val bin_val
333 17 34343
333 18 2343
333 19 7/1/2008
333 20 7/14/2008

So this shows the same entry as above but with the new structure. So all items with this structure have prop_id values between 17 and 20, each prop_id representing one variable. The actual variable is held in one of the _val columns.

What I want to be able to do is make a query that will take the new structure and return the result in the same form as the original, so that I can feed those results into another query so the code won't have to change, just the sql statements.

The problem is I don't know how to make this query, I know it involved selecting from the new table where prop_id is 17 to 20, but then I need to merge them somehow into one row and rename the column entries so they look the same as before.

Could anyone help me out? I would really appreciate it.

If I need to explain this any better let me know.

Edit: The alignment is messed up in the tables when I posted it, but it shouldn't make the problem harder to understand.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 12:18:24

[code]SELECT id,
MAX(CASE WHEN prop_id = 17 THEN COALESCE(str_val, CAST(num_val AS varchar(20)), CAST(date_val AS varchar(20)), CAST(bin_val AS varchar(20))) ELSE NULL END) AS Voucher,
MAX(CASE WHEN prop_id = 18 THEN COALESCE(str_val, CAST(num_val AS varchar(20)), CAST(date_val AS varchar(20)), CAST(bin_val AS varchar(20))) ELSE NULL END) AS ProjectCode,
MAX(CASE WHEN prop_id = 19 THEN COALESCE(str_val, CAST(num_val AS varchar(20)), CAST(date_val AS varchar(20)), CAST(bin_val AS varchar(20))) ELSE NULL END) AS Bucket,
MAX(CASE WHEN prop_id = 20 THEN COALESCE(str_val, CAST(num_val AS varchar(20)), CAST(date_val AS varchar(20)), CAST(bin_val AS varchar(20))) ELSE NULL END) AS Document_Date
FROM NewTable
GROUP BY id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

threep
Starting Member

4 Posts

Posted - 2010-03-15 : 14:50:16

Hey, thanks for the response. I've tried it and it works, I used what you did and made it slightly smaller, hoping it would be faster:

SELECT tocid,
MAX(CASE WHEN prop_id = 21 THEN str_val ELSE NULL END) AS Voucher,
MAX(CASE WHEN prop_id = 22 THEN str_val ELSE NULL END) AS Project_Code,
MAX(CASE WHEN prop_id = 23 THEN date_val ELSE NULL END) AS Bucket,
MAX(CASE WHEN prop_id = 24 THEN date_val ELSE NULLEND) AS Document_Date
FROM dbo.propval
WHERE (prop_id >= 21) AND (prop_id <= 24)
GROUP BY tocid

The only issue I have now, is that since I have functions in a web app that query this view, it is considerably slower than it used to be in the old version of the database structure. So I've been trying to speed it up somehow. I turned on schemabinding (I'm not sure if this speeds it up or not), and tried to make an index on the tocId but couldn't because of the max clause?

Any way I should look about speeding this view up, I'm not sure where to go.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 00:10:25

what all indexes you've on propval?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

threep
Starting Member

4 Posts

Posted - 2010-03-16 : 08:35:31

ok, so in propval which is the table that the view is based on, there is a clustered index on tocid prop_id and pos (which i don't use in my view at the moment, only the tocid is in the view). There are several more nonclustered indexes most of which use the propid and tocid fields.

So if I want to index the view on the propval table does that mean i need to have all of the columns of it's clustered index?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 09:22:20

what does execution plan show? what are costly steps?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

threep
Starting Member

4 Posts

Posted - 2010-03-16 : 09:54:43

Hmm ok I didn't know about execution plans before you mentioned them (very new to this).

Stream aggregate takes 7%, computer scalar 11%, and clustered index scan 82% so it is the most costly piece.

I'm not sure if this is relevant information or not, but the only purpose of this view is to do an additional select statement on it, so it will never be looked at directly or need to be in any sort of order if that can make it faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources