some one plz help me.
I had a table with these columns.
Table(Id int,Name varchar,Value Varchar).
I have to group them by ID and each Name becomes column name of the new table
ex:-
Id Name Value
-------
1 x a1
2 x a2
3 x a3
1 y b1
2 y b2
3 y b3
1 z c1
2 z c2
3 z c3
I need it in this way
x y z
----
a1 b1 c1
a2 b2 c2
a3 b3 c3
(no of columns in the new table can't be pre determined)
and which one would be better option to do this
in VB.Net code or in a Storedprocedure?You can do this in a stored procedure using CASE statements. You can find a fine explanation of the method by searching for "Cross-Tab Reports" in Books online.|||Normally I'd recommend that you do this on the client, but it can (usually) be done on the server. Just to prove that, I wrote a snippet of code that seems to work, although it doesn't deal well with ill-behaved data. FWIW, my code is:CREATE TABLE tPivot (
id INT NOT NULL
, name VARCHAR(20) NOT NULL
, value VARCHAR(20) NOT NULL
)
INSERT tPivot (id, name, value) VALUES (1, 'x', 'A1')
INSERT tPivot (id, name, value) VALUES (2, 'x', 'A2')
INSERT tPivot (id, name, value) VALUES (3, 'x', 'A3')
INSERT tPivot (id, name, value) VALUES (1, 'y', 'B1')
INSERT tPivot (id, name, value) VALUES (2, 'y', 'B2')
INSERT tPivot (id, name, value) VALUES (3, 'y', 'B3')
INSERT tPivot (id, name, value) VALUES (1, 'z', 'C1')
INSERT tPivot (id, name, value) VALUES (2, 'z', 'C2')
INSERT tPivot (id, name, value) VALUES (3, 'z', 'C3')
DECLARE @.cmd NVARCHAR(4000)
DECLARE @.cName SYSNAME
SELECT @.cmd = 'SELECT DISTINCT id'
DECLARE zName CURSOR FOR SELECT DISTINCT
name
FROM tPivot
OPEN zName
FETCH zName INTO @.cName
WHILE 0 = @.@.fetch_status
BEGIN
SELECT @.cmd = @.cmd +
', (SELECT Min(value) FROM tPivot AS b'
+ ' WHERE b.id = a.id'
+ ' AND b.name = ''' + @.cName
+ ''') AS [' + @.cName + ']'
FETCH zName INTO @.cName
END
CLOSE zName
DEALLOCATE zName
SELECT @.cmd = @.cmd + ' FROM tPivot AS a'
SELECT @.cmd
EXECUTE (@.cmd)Note that even though this CAN be done on the server, it would be better handled on the client side in most cases.
-PatP|||Hey Pat, you just love cursors, don't you?
declare @.tbl table (
ID int not null,
Name char(1) not null,
Value char(2) not null)
insert @.tbl values(1, 'x', 'a1')
insert @.tbl values(2, 'x', 'a2')
insert @.tbl values(3, 'x', 'a3')
insert @.tbl values(1, 'y', 'b1')
insert @.tbl values(2, 'y', 'b2')
insert @.tbl values(3, 'y', 'b3')
insert @.tbl values(1, 'z', 'c1')
insert @.tbl values(2, 'z', 'c2')
insert @.tbl values(3, 'z', 'c3')
select [x], [y], [z] from (
select distinct ID from @.tbl) t1
left outer join (
select ID, [x] = Value from @.tbl where Name = 'x') t2
on t1.ID = t2.ID
left outer join (
select ID, [y] = Value from @.tbl where Name = 'y') t3
on t1.ID = t3.ID
left outer join (
select ID, [z] = Value from @.tbl where Name = 'z') t4
on t1.ID = t4.ID|||Originally posted by rdjabarov
Hey Pat, you just love cursors, don't you? Not hardly, hate 'em with a passion. Unfortunately when I read the specs, I couldn't think of another way to deal with unknown column names.
-PatP|||Who loves left-joins and subqueries?
declare @.tbl table (
ID int not null,
Name char(1) not null,
Value char(2) not null)
insert @.tbl values(1, 'x', 'a1')
insert @.tbl values(2, 'x', 'a2')
insert @.tbl values(3, 'x', 'a3')
insert @.tbl values(1, 'y', 'b1')
insert @.tbl values(2, 'y', 'b2')
insert @.tbl values(3, 'y', 'b3')
insert @.tbl values(1, 'z', 'c1')
insert @.tbl values(2, 'z', 'c2')
insert @.tbl values(3, 'z', 'c3')
select max(case when Name = 'x' then Value end) as x,
max(case when Name = 'y' then Value end) as y,
max(case when Name = 'z' then Value end) as z
from @.tbl
group by ID|||Originally posted by theguru
(no of columns in the new table can't be pre determined)
Was this part of the spec optional ?
-PatP|||Originally posted by Pat Phelan
Not hardly, hate 'em with a passion. Unfortunately when I read the specs, I couldn't think of another way to deal with unknown column names.
-PatP
I saw the spec and stayed away...
Blenderized data anyone?
Salt or No Salt?|||Originally posted by Brett Kaiser
Salt or No Salt? Stayed away ? Don't you mean you're wasting away... oops, wait a sec, you're headed there anyway!
-PatP|||Not until 5:00 Pat...
Hey, look at that...it's 5:00!
See ya....|||"(no of columns in the new table can't be pre determined)"
Crap. Always read the fine print...
theguru, I've seen posts for fully dynamic SQL code that will do on-demand cross tabs, though I haven't tested them. rdjabarov, didn't you have one? It is definitely advanced SQL programming, so if one of these other gentlemen cannot refer you to some prewritten code, I suggest you try to accomplish this in VB.Net, or wait for SQL Server Yukon to be released.|||Whenever I see something like "the number of columns cannot be pre..." I just can't believe there is a developer that can actually buy it! You mean to say, that the number of columns can be 432347656? Or even more realistic, like 2972? Isn't it indicative of both poor app and poor database design? But I'd just stress the first one, - who in their sober mind would design an application that would produce such output?|||Originally posted by blindman
It is definitely advanced SQL programming, so if one of these other gentlemen cannot refer you to some prewritten code, I suggest you try to accomplish this in VB.Net, or wait for SQL Server Yukon to be released. At least I think that is what my code sample does. That is exactly why I had to resort to a cursor to build dynamic code, even though both of those go against my better judgement!
-PatP|||Whoa! Please go get your cig!
People ask this question because
A) They are trying to format the data for reporting
and
2) They were weaned on MS Access and its wonderfully convenient and fully dynamic cross-tab functionality.
Problem is, theguru, that when you don't know the number of columns or the names of the columns, most reporting applications (such as Crystal or even MS Access' reports) will choke on the output.
Perhaps your best bet would be to load the data into a pivot table in flat-file format, and then slice-and-dice however you want. How pretty does the output need to be?
Pat Phelan, I like your idea, though I haven't tried it out. I'd call it semi-dynamic, since you are working with a defined table format. A fully-dynamic method applicable to any dataset is the Holy Grail of cross-tab reporting.|||Originally posted by rdjabarov
Isn't it indicative of both poor app and poor database design? I'm not prepared to argue that point. We'd both be "preaching to the choir" on this one!
Originally posted by rdjabarov
But I'd just stress the first one, - who in their sober mind would design an application that would produce such output? What makes you think that the designer was in their sober mind ? ;)
-PatP|||Hey, at least my problems can be answered with a cig! You guys are sitting on a poor design and kicking this horse with "non-determined" number of legs wondering if it's gonna ever run again (tsk, it's been dead for a while...)|||See what happens when we both start typing really fast?|||Originally posted by rdjabarov
See what happens when we both start typing really fast? Yeah, but it's fun to watch!
-PatP|||Thank you all for u r replies.
Well "the number of columns cannot be pre..." doesn't mean that no of columns may exceed a 2 digit number atmost 20 columns.
ok I will try this out in stored procedure with my actual data.
thanks once again..keep sending u r suggestions...
Originally posted by rdjabarov
Whenever I see something like "the number of columns cannot be pre..." I just can't believe there is a developer that can actually buy it! You mean to say, that the number of columns can be 432347656? Or even more realistic, like 2972? Isn't it indicative of both poor app and poor database design? But I'd just stress the first one, - who in their sober mind would design an application that would produce such output?|||sorry, I think I might be missing some thing here.
This will work when I am sure of occurance x,y,z names exactly in the table
but it is not case here x,y,z may be reffered with some other names like A,B,C or O,P,Q which can't be presumed.
Originally posted by blindman
Who loves left-joins and subqueries?
declare @.tbl table (
ID int not null,
Name char(1) not null,
Value char(2) not null)
insert @.tbl values(1, 'x', 'a1')
insert @.tbl values(2, 'x', 'a2')
insert @.tbl values(3, 'x', 'a3')
insert @.tbl values(1, 'y', 'b1')
insert @.tbl values(2, 'y', 'b2')
insert @.tbl values(3, 'y', 'b3')
insert @.tbl values(1, 'z', 'c1')
insert @.tbl values(2, 'z', 'c2')
insert @.tbl values(3, 'z', 'c3')
select max(case when Name = 'x' then Value end) as x,
max(case when Name = 'y' then Value end) as y,
max(case when Name = 'z' then Value end) as z
from @.tbl
group by ID|||Great...I go out and slam some 'ritas...get called back in...yeah there;s a concept..to fix a prod problem and everyone is going nuts...
The point is mute...
It's still bender data...
No?|||Just curious at this point, but have you tried my code with your data?
-PatP|||yup,
thank u.but I am waiting for some more options.
any way I will use this one for the time being.
thank u.
Originally posted by Pat Phelan
Just curious at this point, but have you tried my code with your data?
-PatP|||Just a note...
1. I'm exhausted...
2. What's the point of your result set? It makes no sense.
Is this homework?|||This is what i need to show to my client.
I can't change the DB design at this point.I have to do this at any cost, performance is an exception for this.
Originally posted by Brett Kaiser
Just a note...
1. I'm exhausted...
2. What's the point of your result set? It makes no sense.
Is this homework?
No comments:
Post a Comment