|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Denormalizing a database tableAny help with creating a SP that denormalizes the below table to the desired output will be appreciated. Current Input Table Structure: SNum KeyName KeyVal value 1 SCM1 name DFW 1 SCM1 OVal 200 1 SCM1 OSum 500 2 SCM2 name ORL 2 SCM2 OVal 100 2 SCM2 OSum 200 3 SCM1 name DFW 3 SCM1 OVal 100 3 SCM1 OSum 100 Desired O/p in a secondary temp table DFW 200 500 ORL 100 200 DFW 100 100 Rgds Alk Try:
SELECT MAX( CASE WHEN KeyVal = 'name' THEN value END ), MAX( CASE WHEN KeyVal = 'OVal' THEN value END ), MAX( CASE WHEN KeyVal = 'OSum' THEN value END ) FROM tbl GROUP BY SNum ; -- Anith Alk
create table #test ( snum int, keyname varchar(10), keyval varchar(10), value varchar(10) ) insert into #test values (1,'SCM1','name','DFW') insert into #test values (1,'SCM1','OVal','200') insert into #test values (1,'SCM1','OSum','500') insert into #test values (2,'SCM2','name','ORL') insert into #test values (2,'SCM2','OVal','100') insert into #test values (2,'SCM2','OSum','200') insert into #test values (3,'SCM1','name','DFW') insert into #test values (3,'SCM1','OVal','100') insert into #test values (3,'SCM1','OSum','100') select snum, max(case when keyval='name' then value end) as '1' , max(case when keyval='OVal' then value end) as '2' , max(case when keyval='OSum' then value end) as '3' from #test group by snum Show quote "Alk" <alkkmrz2***@yahoo.com> wrote in message news:1155084696.726623.113990@n13g2000cwa.googlegroups.com... > Folks, > > Any help with creating a SP that denormalizes the below table to the > desired output will be appreciated. > > Current Input Table Structure: > > SNum KeyName KeyVal value > 1 SCM1 name DFW > 1 SCM1 OVal 200 > 1 SCM1 OSum 500 > 2 SCM2 name ORL > 2 SCM2 OVal 100 > 2 SCM2 OSum 200 > 3 SCM1 name DFW > 3 SCM1 OVal 100 > 3 SCM1 OSum 100 > > > Desired O/p in a secondary temp table > > DFW 200 500 > ORL 100 200 > DFW 100 100 > > Rgds > Alk > Thx Uri,
Adding a lil bit more complexity My Source Table1 has SNum KeyName KeyVal value 1 SCM1 name DFW 1 SCM1 OVal 200 1 SCM1 OSum 500 2 SCM2 name ORL 2 SCM2 OVal 100 2 SCM2 OSum 200 3 SCM1 name DFW 3 SCM1 OVal 100 3 SCM1 OSum 100 4 SCM1 name DFW 4 SCM1 OVal 300 4 SCM1 OSum 300 My Source Table 2 has SNum Date 1 01/01/2006 2 01/01/2006 3 01/01/2006 4 02/01/2006 Desired Output DFW 300 600 01/01/2006 ORL 100 200 01/01/2006 DFW 300 300 02/01/2006 In other words, denormalizing the data and also aggregating the data in source table1 using data from source table2 Hope it is not confusing. Thx Alk Uri Dimant wrote: Show quote > Alk > create table #test ( > snum int, > keyname varchar(10), > keyval varchar(10), > value varchar(10) > ) > > insert into #test values (1,'SCM1','name','DFW') > insert into #test values (1,'SCM1','OVal','200') > insert into #test values (1,'SCM1','OSum','500') > > insert into #test values (2,'SCM2','name','ORL') > insert into #test values (2,'SCM2','OVal','100') > insert into #test values (2,'SCM2','OSum','200') > > insert into #test values (3,'SCM1','name','DFW') > insert into #test values (3,'SCM1','OVal','100') > insert into #test values (3,'SCM1','OSum','100') > > > > select snum, > max(case when keyval='name' then value end) as '1' , > max(case when keyval='OVal' then value end) as '2' , > max(case when keyval='OSum' then value end) as '3' > from #test > group by snum > > > "Alk" <alkkmrz2***@yahoo.com> wrote in message > news:1155084696.726623.113990@n13g2000cwa.googlegroups.com... > > Folks, > > > > Any help with creating a SP that denormalizes the below table to the > > desired output will be appreciated. > > > > Current Input Table Structure: > > > > SNum KeyName KeyVal value > > 1 SCM1 name DFW > > 1 SCM1 OVal 200 > > 1 SCM1 OSum 500 > > 2 SCM2 name ORL > > 2 SCM2 OVal 100 > > 2 SCM2 OSum 200 > > 3 SCM1 name DFW > > 3 SCM1 OVal 100 > > 3 SCM1 OSum 100 > > > > > > Desired O/p in a secondary temp table > > > > DFW 200 500 > > ORL 100 200 > > DFW 100 100 > > > > Rgds > > Alk > > Alk,
Your original data is not normalized. What you are trying to accomplish is not, repeat not, denormalizing... Alexander,
Let's just say data is not normalized and the subject of this discussion "Denormalizing" is a misnomer, Do you have any suggestions for the updated problem? Thx Alk Alexander Kuznetsov wrote: Show quote > Alk, > > Your original data is not normalized. What you are trying to accomplish > is not, repeat not, denormalizing... Alk
select #test.snum,dt, max(case when keyval='name' then value end) as '1' , max(case when keyval='OVal' then value end) as '2' , max(case when keyval='OSum' then value end) as '3' from #test join #test1 on #test.snum=#test1.snum group by #test.snum,dt Show quote "Alk" <alkkmrz2***@yahoo.com> wrote in message news:1155168617.525771.274060@n13g2000cwa.googlegroups.com... > Alexander, > > Let's just say data is not normalized and the subject of this > discussion "Denormalizing" is a misnomer, > > Do you have any suggestions for the updated problem? > > Thx > Alk > Alexander Kuznetsov wrote: >> Alk, >> >> Your original data is not normalized. What you are trying to accomplish >> is not, repeat not, denormalizing... > Building on Uri's example, here's my best guess. Because the
value column is varchar(10) and you need to sum some of the values it contained, you must convert them to a number type. I've used 1* to convert them to integers, but if they might be decimals or another type, you could more carefully convert the result of the CASE expression with CAST. create table #test1 ( snum int, keyname varchar(10), keyval varchar(10), value varchar(10) ) insert into #test1 values (1,'SCM1','name','DFW') insert into #test1 values (1,'SCM1','OVal','200') insert into #test1 values (1,'SCM1','OSum','500') insert into #test1 values (2,'SCM2','name','ORL') insert into #test1 values (2,'SCM2','OVal','100') insert into #test1 values (2,'SCM2','OSum','200') insert into #test1 values (3,'SCM1','name','DFW') insert into #test1 values (3,'SCM1','OVal','100') insert into #test1 values (3,'SCM1','OSum','100') insert into #test1 values (4,'SCM1','name','DFW') insert into #test1 values (4,'SCM1','OVal','300') insert into #test1 values (4,'SCM1','OSum','300') create table #test2 ( snum int, [Date] datetime ) insert into #test2 values (1,'20060101') insert into #test2 values (2,'20060101') insert into #test2 values (3,'20060101') insert into #test2 values (4,'20060102') select [1], sum([2]) as [2], sum([3]) as [3], [Date] from ( select snum, max(case when keyval='name' then value end) as '1' , 1*max(case when keyval='OVal' then value end) as '2' , 1*max(case when keyval='OSum' then value end) as '3' from #test1 group by snum ) as t1 join #test2 as t2 on t2.snum = t1.snum group by [1], [Date] go drop table #test1, #test2 -- Steve Kass -- Drew University -- http://www.stevekass.com Alk wrote: Show quote >Thx Uri, > >Adding a lil bit more complexity > > >My Source Table1 has >SNum KeyName KeyVal value >1 SCM1 name DFW >1 SCM1 OVal 200 >1 SCM1 OSum 500 >2 SCM2 name ORL >2 SCM2 OVal 100 >2 SCM2 OSum 200 >3 SCM1 name DFW >3 SCM1 OVal 100 >3 SCM1 OSum 100 >4 SCM1 name DFW >4 SCM1 OVal 300 >4 SCM1 OSum 300 > > >My Source Table 2 has > > >SNum Date >1 01/01/2006 >2 01/01/2006 >3 01/01/2006 >4 02/01/2006 > > >Desired Output >DFW 300 600 01/01/2006 >ORL 100 200 01/01/2006 >DFW 300 300 02/01/2006 > > >In other words, denormalizing the data and also aggregating the data in > >source table1 using data from source table2 > > >Hope it is not confusing. > > >Thx >Alk > > > >Uri Dimant wrote: > > >>Alk >>create table #test ( >>snum int, >>keyname varchar(10), >> keyval varchar(10), >>value varchar(10) >>) >> >>insert into #test values (1,'SCM1','name','DFW') >>insert into #test values (1,'SCM1','OVal','200') >>insert into #test values (1,'SCM1','OSum','500') >> >>insert into #test values (2,'SCM2','name','ORL') >>insert into #test values (2,'SCM2','OVal','100') >>insert into #test values (2,'SCM2','OSum','200') >> >>insert into #test values (3,'SCM1','name','DFW') >>insert into #test values (3,'SCM1','OVal','100') >>insert into #test values (3,'SCM1','OSum','100') >> >> >> >>select snum, >> max(case when keyval='name' then value end) as '1' , >> max(case when keyval='OVal' then value end) as '2' , >> max(case when keyval='OSum' then value end) as '3' >>from #test >>group by snum >> >> >>"Alk" <alkkmrz2***@yahoo.com> wrote in message >>news:1155084696.726623.113990@n13g2000cwa.googlegroups.com... >> >> >>>Folks, >>> >>>Any help with creating a SP that denormalizes the below table to the >>>desired output will be appreciated. >>> >>>Current Input Table Structure: >>> >>>SNum KeyName KeyVal value >>>1 SCM1 name DFW >>>1 SCM1 OVal 200 >>>1 SCM1 OSum 500 >>>2 SCM2 name ORL >>>2 SCM2 OVal 100 >>>2 SCM2 OSum 200 >>>3 SCM1 name DFW >>>3 SCM1 OVal 100 >>>3 SCM1 OSum 100 >>> >>> >>>Desired O/p in a secondary temp table >>> >>>DFW 200 500 >>>ORL 100 200 >>>DFW 100 100 >>> >>>Rgds >>>Alk >>> >>> >>> > > > Hi, Steve
How are you? After reading the OP's first request I understood that he wants MAX() to value column , but now i see he added more complexity to return SUM() Well , I will be waiting for a while for his next request Show quote "Steve Kass" <sk***@drew.edu> wrote in message news:ORGOGVDvGHA.2448@TK2MSFTNGP06.phx.gbl... > Building on Uri's example, here's my best guess. Because the > value column is varchar(10) and you need to sum some of the > values it contained, you must convert them to a number type. > I've used 1* to convert them to integers, but if they might > be decimals or another type, you could more carefully > convert the result of the CASE expression with CAST. > > create table #test1 ( > snum int, > keyname varchar(10), > keyval varchar(10), > value varchar(10) > ) > > insert into #test1 values (1,'SCM1','name','DFW') > insert into #test1 values (1,'SCM1','OVal','200') > insert into #test1 values (1,'SCM1','OSum','500') > > insert into #test1 values (2,'SCM2','name','ORL') > insert into #test1 values (2,'SCM2','OVal','100') > insert into #test1 values (2,'SCM2','OSum','200') > > insert into #test1 values (3,'SCM1','name','DFW') > insert into #test1 values (3,'SCM1','OVal','100') > insert into #test1 values (3,'SCM1','OSum','100') > > insert into #test1 values (4,'SCM1','name','DFW') > insert into #test1 values (4,'SCM1','OVal','300') > insert into #test1 values (4,'SCM1','OSum','300') > > create table #test2 ( > snum int, > [Date] datetime > ) > > insert into #test2 values (1,'20060101') > insert into #test2 values (2,'20060101') > insert into #test2 values (3,'20060101') > insert into #test2 values (4,'20060102') > > select > [1], > sum([2]) as [2], > sum([3]) as [3], > [Date] > from ( > select snum, > max(case when keyval='name' then value end) as '1' , > 1*max(case when keyval='OVal' then value end) as '2' , > 1*max(case when keyval='OSum' then value end) as '3' > from #test1 > group by snum > ) as t1 > join #test2 as t2 > on t2.snum = t1.snum > group by [1], [Date] > go > > drop table #test1, #test2 > > -- Steve Kass > -- Drew University > -- http://www.stevekass.com > > Alk wrote: > >>Thx Uri, >> >>Adding a lil bit more complexity >> >> >>My Source Table1 has >>SNum KeyName KeyVal value >>1 SCM1 name DFW >>1 SCM1 OVal 200 >>1 SCM1 OSum 500 >>2 SCM2 name ORL >>2 SCM2 OVal 100 >>2 SCM2 OSum 200 >>3 SCM1 name DFW >>3 SCM1 OVal 100 >>3 SCM1 OSum 100 >>4 SCM1 name DFW >>4 SCM1 OVal 300 >>4 SCM1 OSum 300 >> >> >>My Source Table 2 has >> >> >>SNum Date >>1 01/01/2006 >>2 01/01/2006 >>3 01/01/2006 >>4 02/01/2006 >> >> >>Desired Output >>DFW 300 600 01/01/2006 >>ORL 100 200 01/01/2006 >>DFW 300 300 02/01/2006 >> >> >>In other words, denormalizing the data and also aggregating the data in >> >>source table1 using data from source table2 >> >> >>Hope it is not confusing. >> >> >>Thx >>Alk >> >> >> >>Uri Dimant wrote: >> >>>Alk >>>create table #test ( >>>snum int, >>>keyname varchar(10), >>> keyval varchar(10), >>>value varchar(10) >>>) >>> >>>insert into #test values (1,'SCM1','name','DFW') >>>insert into #test values (1,'SCM1','OVal','200') >>>insert into #test values (1,'SCM1','OSum','500') >>> >>>insert into #test values (2,'SCM2','name','ORL') >>>insert into #test values (2,'SCM2','OVal','100') >>>insert into #test values (2,'SCM2','OSum','200') >>> >>>insert into #test values (3,'SCM1','name','DFW') >>>insert into #test values (3,'SCM1','OVal','100') >>>insert into #test values (3,'SCM1','OSum','100') >>> >>> >>> >>>select snum, >>> max(case when keyval='name' then value end) as '1' , >>> max(case when keyval='OVal' then value end) as '2' , >>> max(case when keyval='OSum' then value end) as '3' >>>from #test >>>group by snum >>> >>> >>>"Alk" <alkkmrz2***@yahoo.com> wrote in message >>>news:1155084696.726623.113990@n13g2000cwa.googlegroups.com... >>> >>>>Folks, >>>> >>>>Any help with creating a SP that denormalizes the below table to the >>>>desired output will be appreciated. >>>> >>>>Current Input Table Structure: >>>> >>>>SNum KeyName KeyVal value >>>>1 SCM1 name DFW >>>>1 SCM1 OVal 200 >>>>1 SCM1 OSum 500 >>>>2 SCM2 name ORL >>>>2 SCM2 OVal 100 >>>>2 SCM2 OSum 200 >>>>3 SCM1 name DFW >>>>3 SCM1 OVal 100 >>>>3 SCM1 OSum 100 >>>> >>>> >>>>Desired O/p in a secondary temp table >>>> >>>>DFW 200 500 >>>>ORL 100 200 >>>>DFW 100 100 >>>> >>>>Rgds >>>>Alk >>>> >>>> >> >> Thx Steve & Uri
Here is the final piece of the complex SQL puzzle that I am trying to unravel, Can the values for columns 2 && 3 be summed by calendar months? In other words the query below is summing by a date value, Can I have a single row for DFW for the month of January with a total of all the values for the month of january? Thx Alk PS: Just to clarify, I am seeking suggestions in a piecemeal way because after receiving the first set of clues, I put in a honest sincere effort to resolve the rest of the problem by myself. Uri Dimant wrote: Show quote > Hi, Steve > > How are you? > > After reading the OP's first request I understood that he wants MAX() to > value column , but now i see he added more complexity to return SUM() > Well , I will be waiting for a while for his next request > > > > > > "Steve Kass" <sk***@drew.edu> wrote in message > news:ORGOGVDvGHA.2448@TK2MSFTNGP06.phx.gbl... > > Building on Uri's example, here's my best guess. Because the > > value column is varchar(10) and you need to sum some of the > > values it contained, you must convert them to a number type. > > I've used 1* to convert them to integers, but if they might > > be decimals or another type, you could more carefully > > convert the result of the CASE expression with CAST. > > > > create table #test1 ( > > snum int, > > keyname varchar(10), > > keyval varchar(10), > > value varchar(10) > > ) > > > > insert into #test1 values (1,'SCM1','name','DFW') > > insert into #test1 values (1,'SCM1','OVal','200') > > insert into #test1 values (1,'SCM1','OSum','500') > > > > insert into #test1 values (2,'SCM2','name','ORL') > > insert into #test1 values (2,'SCM2','OVal','100') > > insert into #test1 values (2,'SCM2','OSum','200') > > > > insert into #test1 values (3,'SCM1','name','DFW') > > insert into #test1 values (3,'SCM1','OVal','100') > > insert into #test1 values (3,'SCM1','OSum','100') > > > > insert into #test1 values (4,'SCM1','name','DFW') > > insert into #test1 values (4,'SCM1','OVal','300') > > insert into #test1 values (4,'SCM1','OSum','300') > > > > create table #test2 ( > > snum int, > > [Date] datetime > > ) > > > > insert into #test2 values (1,'20060101') > > insert into #test2 values (2,'20060101') > > insert into #test2 values (3,'20060101') > > insert into #test2 values (4,'20060102') > > > > select > > [1], > > sum([2]) as [2], > > sum([3]) as [3], > > [Date] > > from ( > > select snum, > > max(case when keyval='name' then value end) as '1' , > > 1*max(case when keyval='OVal' then value end) as '2' , > > 1*max(case when keyval='OSum' then value end) as '3' > > from #test1 > > group by snum > > ) as t1 > > join #test2 as t2 > > on t2.snum = t1.snum > > group by [1], [Date] > > go > > > > drop table #test1, #test2 > > > > -- Steve Kass > > -- Drew University > > -- http://www.stevekass.com > > > > Alk wrote: > > > >>Thx Uri, > >> > >>Adding a lil bit more complexity > >> > >> > >>My Source Table1 has > >>SNum KeyName KeyVal value > >>1 SCM1 name DFW > >>1 SCM1 OVal 200 > >>1 SCM1 OSum 500 > >>2 SCM2 name ORL > >>2 SCM2 OVal 100 > >>2 SCM2 OSum 200 > >>3 SCM1 name DFW > >>3 SCM1 OVal 100 > >>3 SCM1 OSum 100 > >>4 SCM1 name DFW > >>4 SCM1 OVal 300 > >>4 SCM1 OSum 300 > >> > >> > >>My Source Table 2 has > >> > >> > >>SNum Date > >>1 01/01/2006 > >>2 01/01/2006 > >>3 01/01/2006 > >>4 02/01/2006 > >> > >> > >>Desired Output > >>DFW 300 600 01/01/2006 > >>ORL 100 200 01/01/2006 > >>DFW 300 300 02/01/2006 > >> > >> > >>In other words, denormalizing the data and also aggregating the data in > >> > >>source table1 using data from source table2 > >> > >> > >>Hope it is not confusing. > >> > >> > >>Thx > >>Alk > >> > >> > >> > >>Uri Dimant wrote: > >> > >>>Alk > >>>create table #test ( > >>>snum int, > >>>keyname varchar(10), > >>> keyval varchar(10), > >>>value varchar(10) > >>>) > >>> > >>>insert into #test values (1,'SCM1','name','DFW') > >>>insert into #test values (1,'SCM1','OVal','200') > >>>insert into #test values (1,'SCM1','OSum','500') > >>> > >>>insert into #test values (2,'SCM2','name','ORL') > >>>insert into #test values (2,'SCM2','OVal','100') > >>>insert into #test values (2,'SCM2','OSum','200') > >>> > >>>insert into #test values (3,'SCM1','name','DFW') > >>>insert into #test values (3,'SCM1','OVal','100') > >>>insert into #test values (3,'SCM1','OSum','100') > >>> > >>> > >>> > >>>select snum, > >>> max(case when keyval='name' then value end) as '1' , > >>> max(case when keyval='OVal' then value end) as '2' , > >>> max(case when keyval='OSum' then value end) as '3' > >>>from #test > >>>group by snum > >>> > >>> > >>>"Alk" <alkkmrz2***@yahoo.com> wrote in message > >>>news:1155084696.726623.113990@n13g2000cwa.googlegroups.com... > >>> > >>>>Folks, > >>>> > >>>>Any help with creating a SP that denormalizes the below table to the > >>>>desired output will be appreciated. > >>>> > >>>>Current Input Table Structure: > >>>> > >>>>SNum KeyName KeyVal value > >>>>1 SCM1 name DFW > >>>>1 SCM1 OVal 200 > >>>>1 SCM1 OSum 500 > >>>>2 SCM2 name ORL > >>>>2 SCM2 OVal 100 > >>>>2 SCM2 OSum 200 > >>>>3 SCM1 name DFW > >>>>3 SCM1 OVal 100 > >>>>3 SCM1 OSum 100 > >>>> > >>>> > >>>>Desired O/p in a secondary temp table > >>>> > >>>>DFW 200 500 > >>>>ORL 100 200 > >>>>DFW 100 100 > >>>> > >>>>Rgds > >>>>Alk > >>>> > >>>> > >> > >>  |
|||||||||||||||||||||||
Other interesting topics