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.
Go to Top of Page

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/

Go to Top of Page

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
Go to Top of Page

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';
Go to Top of Page

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/

Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -