January 30, 2012 at 10:40 am
How can i get my table to show the results i want below? Pivot table? I've never used pivot before.
Table:
tocidprop_idstr_valnum_val
1059786104USDNULL
1059786105Pricing Null
1059786108NULL1539.17
results:
tocid str_val str_val num_val
1059786 USD Pricing 1539.17
January 30, 2012 at 12:46 pm
GrassHopper (1/30/2012)
How can i get my table to show the results i want below? Pivot table? I've never used pivot before.Table:
tocidprop_idstr_valnum_val
1059786104USDNULL
1059786105Pricing Null
1059786108NULL1539.17
results:
tocid str_val str_val num_val
1059786 USD Pricing 1539.17
Not needed, you can use "prop_id" to pick up each one of the values for your report; provided 104 means "currency", etc.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 30, 2012 at 1:07 pm
Can you show me an example of what you mean? I don't follow you.
January 31, 2012 at 8:04 am
GrassHopper (1/30/2012)
Can you show me an example of what you mean? I don't follow you.
Probably a dozen ways to do it, here goes one...
create table #mytable
(tocid decimal,
prop_id decimal,
str_val varchar(20),
num_val numeric);
insert into #mytable values(1059788, 104, 'USD', Null);
insert into #mytable values(1059788, 105, 'Pricing', Null);
insert into #mytable values(1059788, 108, Null, 1539);
insert into #mytable values(1059799, 104, 'USD', Null);
insert into #mytable values(1059799, 105, 'Pricing', Null);
insert into #mytable values(1059799, 108, Null, 2345);
select a.tocid,
b.str_val,
c.num_val
from #mytable a,
#mytable b,
#mytable c
where a.prop_id = 104
and b.prop_id = 105
and c.prop_id = 108
and a.tocid = b.tocid
and a.tocid = c.tocid;
...and index on (tocid,prop_id) will make it scalable.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 31, 2012 at 8:15 am
PaulB-TheOneAndOnly (1/31/2012)
GrassHopper (1/30/2012)
Can you show me an example of what you mean? I don't follow you.Probably a dozen ways to do it, here goes one...
create table #mytable
(tocid decimal,
prop_id decimal,
str_val varchar(20),
num_val numeric);
insert into #mytable values(1059788, 104, 'USD', Null);
insert into #mytable values(1059788, 105, 'Pricing', Null);
insert into #mytable values(1059788, 108, Null, 1539);
insert into #mytable values(1059799, 104, 'USD', Null);
insert into #mytable values(1059799, 105, 'Pricing', Null);
insert into #mytable values(1059799, 108, Null, 2345);
select a.tocid,
b.str_val,
c.num_val
from #mytable a,
#mytable b,
#mytable c
where a.prop_id = 104
and b.prop_id = 105
and c.prop_id = 108
and a.tocid = b.tocid
and a.tocid = c.tocid;
...and index on (tocid,prop_id) will make it scalable.
You've over-complicated that a bit Paul. You can do it all with 1 hit on the table instead, using CASE.
SELECT tocid,
MAX(CASE WHEN prop_id = 105 THEN str_val ELSE NULL END) AS str_val,
MAX(CASE WHEN prop_id = 108 THEN num_val ELSE NULL END) AS num_val
FROM #mytable
GROUP BY tocid
==INNER JOINS
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#mytable'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
==Using MAX CASE
Table '#mytable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
January 31, 2012 at 8:20 am
Cadavre (1/31/2012)
You've over-complicated that a bit Paul. You can do it all with 1 hit on the table instead, using CASE.
dozen ways minus two 😀
By the way, first solution generates physical I/O only for "a" version of the table, data from "b" and "c" versions comes from buffer cache.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 31, 2012 at 8:47 am
To simplify the question I showed 3 columns but in reality I will be using 170 columns I need to query.....so with that said, which solution will be the most effecient?
This is a sample of the table that holds the column info for the table which holds the actual data. (L = Lookup, I = Integer, S = String, D = Date, N = Numeric)
prop_idprop_nameprop_typeprop_len
13RegionL13
14Client NumberL7
15Client NameL40
16Audit YearL4
17Audit NumberL11
18Vendor RankI40
19Vendor NumberS30
20Vendor NameS40
21Document TypeL24
22OwnerL40
29Document SecurityL18
30Auditor IDS8
31Audit TypeL18
32Client DivisionS40
33GSTNNULL
34HSTNNULL
35QSTNNULL
38Cancel ReasonL43
39LanguageS20
40CreatorS40
41Mail CodeL2
42CY DateDNULL
January 31, 2012 at 9:02 am
I would adopt the solution that...
1- I can understand
2- I can explain to others
3- I feel comfortable I can maintain.
Having said that, I would probably hide the mmhhh... how to put it... unusual design behind a set of views that expose data in a 3NF fashion.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 31, 2012 at 9:04 am
Test, test and test some more!!
When you figure out which of the multitude of solutions works best for your situation, that's the one to use.
January 31, 2012 at 10:51 am
Thanks to both...your replies have been very helpful.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply