Build a Single combined row from several by key

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

SQL Server 2005 Forums

Transact-SQL (2005)

Build a Single combined row from several by key

Author

Topic

adh
Starting Member

4 Posts

Posted - 2012-10-06 : 14:10:53

I have several rows (up to 15 per customer)
Want to build a single row from them like:
Customer, seq#, val1, val2, val3 ...... (seq#=sequence # from 1 to 15 may be less or none per customer)
A 1 12 21 33 ....
A 2 56 14 85
A 3 75 ....
B .....
Need a Single row
Customer Val1_1, Val1_2, val1_3, val2_1, val2_2, val3_3, val3_1......
A 12 21 33 56 14 85 75 ....
B ...
Pleas a fast SQL2005 script.
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-06 : 18:43:10

You can use the PIVOT operator (depending on the details of what you want to do), or use the dynamic pivot operator that Madhivanan has in his blog here: http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

If you were to use Madhivanan's code, you would first need to copy that code and run it to install the stored procedure. Then, you would call the stored procedure like this:

EXEC dbo.CrossTab
	'SELECT Customer,seqNo,val1, val2, val3 ... FROM #tmp',
	'seqNo',
	'max(val1)[], max(val2)[],max(val3)[] ....','Customer'

Regarding your comment about needing something that is fast: I have not worked extensively with Madhivanan's code, so I don't know how fast it is, but it works and it works reliably.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-07 : 20:33:00

quote:
Originally posted by adh

I have several rows (up to 15 per customer)
Want to build a single row from them like:
Customer, seq#, val1, val2, val3 ...... (seq#=sequence # from 1 to 15 may be less or none per customer)
A 1 12 21 33 ....
A 2 56 14 85
A 3 75 ....
B .....
Need a Single row
Customer Val1_1, Val1_2, val1_3, val2_1, val2_2, val3_3, val3_1......
A 12 21 33 56 14 85 75 ....
B ...
Pleas a fast SQL2005 script.
Thanks

this is another way. you could extend it to make it dynamic


declare @test table
(
Customer char(1), 
seq# int, 
val1 int, 
val2 int, 
val3 int
)
insert @test 
select 'A', 1, 12, 21, 33 union all
select 'A',  2, 56, 14, 85

select *
from
(
select Customer,valcat + '_' + cast(seq# as varchar(2)) as cat,val
from @test
unpivot (val for valcat in (val1,val2,val3))u
)t
pivot(max(val) for cat in (val1_1,val2_1,val3_1,val1_2,val2_2,val3_2))p



Customer	val1_1	val2_1	val3_1	val1_2	val2_2	val3_2
--------------------------------------------------------------
A	12	21	33	56	14	85

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

adh
Starting Member

4 Posts

Posted - 2012-10-09 : 05:08:09

Tried to tun your code as is, got Error
Incorrect syntax near '('.
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-09 : 06:52:22

quote:
Originally posted by adh

Tried to tun your code as is, got Error
Incorrect syntax near '('.
Thanks

Which code, the one Visakh posted, or the one I posted? In either case you have to be on a server that is SQL 2005 or later and on a database whose compatibility level is 90 or greater. You can find the server version and compatibility version using the two sql queries below:

SELECT @@version;
EXEC sp_dbcmptlevel 'YourDatabaseNameHere';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-09 : 23:02:10

I also hope its issue with compatibility level or version as I posted the code which I tested and worked fine in my system

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

adh
Starting Member

4 Posts

Posted - 2012-10-10 : 05:42:21

quote:
Originally posted by visakh16

I also hope its issue with compatibility level or version as I posted the code which I tested and worked fine in my system

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

The compatibility level is 80, on a customers server.
He claims it is needed for backward compatibility.
Is this true?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-10 : 06:53:26

quote:

The compatibility level is 80, on a customers server.
He claims it is needed for backward compatibility.
Is this true?

That is quite possible. A number of features were removed in SQL 2005 - see this page: http://msdn.microsoft.com/en-us/library/ms144262(v=sql.90).aspx

Some of these features could still be accessed if compatibility level is kept at 80. As an example, the old style joins ( *=, =* etc.) can still be used in SQL 2005, but only if the database compatibility level is 80.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-10 : 07:19:43

To repeat what I said in my earlier post: changing the compatibility level on a customer database, especially when the customer advises against it should not be done.

Another approach, if you have the flexibility/permissions to do this is as follows: On the same server where the customer database is located, create an auxiliary database with compatibility level = 90. Then, install your function or run your queries in that database but using the tables in the customer database. You can refer to the tables in the customer database using the 3-part naming convention. So for example:

USE YourAuxiliaryDatabase
GO
SELECT TOP 10 * FROM CustomerDatabase.dbo.SomeTable;

If you do it this way, you will be able to use PIVOT operator or use Madhivanan's function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources