| Author |
 Topic  |
|
|
adh
Starting Member
Israel
4 Posts |
 Posted - 10/06/2012 : 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 |
Edited by - adh on 10/06/2012 14:14:02
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/06/2012 : 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
India
47036 Posts |
Posted - 10/07/2012 : 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
Israel
4 Posts |
Posted - 10/09/2012 : 05:08:09
|
Tried to tun your code as is, got Error
Incorrect syntax near '('.
Thanks |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/09/2012 : 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
India
47036 Posts |
Posted - 10/09/2012 : 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
Israel
4 Posts |
Posted - 10/10/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/10/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/10/2012 : 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. |
|
|
| |
Topic |
|
|
|
Build a Single combined row from several by key
