SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Build a Single combined row from several by key
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adh
Starting Member

Israel
4 Posts

Posted - 10/06/2012 :  14:10:53  Show Profile  Reply with Quote
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

Edited by - adh on 10/06/2012 14:14:02

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/06/2012 :  18:43:10  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/07/2012 :  20:33:00  Show Profile  Reply with Quote
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

Israel
4 Posts

Posted - 10/09/2012 :  05:08:09  Show Profile  Reply with Quote
Tried to tun your code as is, got Error
Incorrect syntax near '('.
Thanks
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  06:52:22  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/09/2012 :  23:02:10  Show Profile  Reply with Quote
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

Israel
4 Posts

Posted - 10/10/2012 :  05:42:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/10/2012 :  06:53:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/10/2012 :  07:19:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000