Pivot Cross Tab for Text Results
-
Heh... David, it is I who is not worthy... thank you for your continued compliments. You made my whole day! Good "seeing" you, again.
Anyway, folks... considering the trully variable nature of the number of columns that Ian has posted, this (maybe) could be done without a dynamic cross-tab depending on what Ian actually wants for an output.
Ian, you posted the results you wanted as Comma Separated Values... is that what you actually want? Perhaps if you told me where the output would be used, there are several different "less-dynamic" methods of doing this...
--Jeff Moden
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry I haven't been back to this post, I've been swamped at work this week. If you're in a hurry, I would do a Sergiy suggests and just make the example dynamic (or check out Jeff's approach). Sergiy's good a good point, it would be a great excercise. And, if it ever goes wrong, you'll have a better understanding of what the code does.
If you're still stuck on this, post back, we're all here to help!
SQL guy and Houston Magician
-
Playing the devils advocate... Jeff Modens SQL is absolutely a Must to know... There was - however - the comment that the MIN does not work when having more answers in one session ID or - taking jeffs script - if I have to HDDs per a configuration Item.
How to rewrite the query to have the value filling up, like:
CI_ITEM |HDD |Monitor
Computer1 |20GB |19'' thelonious monk
Computer1 |120GB|NULL
Is that possible? Because the aggregate function does not now which one to take (min / max...etc)
-
no ideas here? or am I just to stupid, I would appreciate this answer as well
-
Well - you'd have to introduce something new to make that work. The purpose of PIVOT or cross-tabulation (or the older syntax in 2000) is to produce a single value per combination of (column header, row headers), or generically per unique tuple of the GROUP BY fields. More values means you would need something added into the GROUP BY, but without that you then start skirting pretty close to "cross-tabulation ain't the right way to do that".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
pity - yeah understand. but the problem of transforming rows to columns would exist in either way... why can't people just do normalized tables
-
how to do it then? its basically the same point of start but I have more values per ID
-
Well since we're working in theoreticals - try this one on for size. How you USE it is a different story.
Asset table: <assetID>, <assetname>, <whatever else>
component type: <comptypeID>, <comptype>,<whatever else>
Asset components: <assetCompID>, <AssetID>, <CompTypeID>, <rank>, <description>
Rank is would be used to differentiate multiple comps of the same type from each other, so that if you had two or more of the same compTypeID in the same AssetID, you'd have a list like
comp1;HD;1; 20GB WestDig
comp1;HD;2;120GB SEAGATE
Comp1;RAM;1;1GB PNY
etc....
in which case, you could use the techniques described before, but grouping by assetID+rank on the row side, and comptype on the column side. Yes - it's simply a method for cheating, but cheating DOES count when you need certain results.
Keep in mind, rank could even be determined on the fly, but performance drops dramatically when you have to do that - it would like be better to have that done during insert (i.e. once per record), and not once per time this thing is run. How you come up with RANK isn't important per se, just as long as it makes multiple "same components in same machine" be distinct.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
did have a thought on ranking, but as the table is being provided AS IS (why do people always report on 3rd party products databases ....) I must accept the performance loss. Or another great idea is around.
Wouldn't call it theoreticals, i have exactly this problem for providing the data for a report. But at the momement we're doing some crazy crystal stuff... which no-one understands...
I've got exactly the same starting point but with multiple entries per ciItem
-
well - if you were to get this anywhere near a SQL 2005 DB (instead of ole 2000), you could read that very interesting article in BOL about "creating custom aggregator functions", which would (say, by using cut and paste from the sample), allow you to create a custom aggregate to for example, concatenate all of those into ONE field.
so:
comp1 ; 'HD'; <20GB WestDig,120GB SeaGate>; .........
again not quite what you were talking about, but it becomes a way to not "lose" text data to a "min" statement for example.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Wouldn't have a problem at all if the HD's were labeled correctly as HD1, HD2, HD3, etc
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, wise guy - tell that the vendor! How easy our lives would be when people would think, ain't it?
Could imagine that I can improve performance in re-labeling this thing and putting in another table. So no realtime data, i will just load it up.
Matt, appreciate it. As you know, sometimes you cannot just go to the store and buy a 2005 to put it in the rack. So the good 'ol 2000 has to deal until people realize that it woulld have a significant benefit to the enterprise.
-
I frequently DO tell the vendor 😛 Many times, I'll find an alternate that does the job right and start migrating business from the uncooperative vendor. For sole source vendors, I teach them how to do it right and find that, many times, they are not only cooperative and understanding, but actually appreciative. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
...sheesh... and I thought EVERYONE had 20K just laying around to buy a 4-processor license of SQL 2005 at their discretion:P... (Trust me I understand)
It was more along the lines of in case you might be posting to the older version, "just because". Otherwise - I'd put it on my "here's all the cool stuff we COULD be doing if you'd only LET us upgrade this thing" list, which I whip out and parade in front of cliewnt at any opportunity:).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
bah... it depends on the bureaucracy in the enterprise.... Never heard of IT Governance and Standard utilities ? :w00t:
However, do you have a cool trick to relabel the diffrent entries with the least performance impact? Just to know before I will use my low knowledge to do it
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply