I need to take row data from a table and create a "column" view

Question

Post reply

I need to take row data from a table and create a "column" view

randyetheridge

SSCommitted

Points: 1703

August 30, 2018 at 7:05 am

#415524

I have a table tdataseg.

Partitionkey VARCHAR(255) NOTNULL,

PeriodKey DATE,

Account VARCHAR(255) NOT NULL,

Entity VARCHAR(255) NOT NULL,

Entityx VARCHAR(255) NOT NULL,

Udx1 VARCHAR(255) NOTNULL,

Rule_ID VARCHAR(255) NOTNULL,

Amount decimal (29,12)NOT NULL

it has monthly data

7	2018-01-31 00:00:00.00	73870802500001	1310-100	738708	33708	UNISOURCE CANADA	27	26056794.32
7	2018-02-28 0:00:00.000	73870802500001	1310-100	738708	33708	UNISOURCE CANADA	27	24824338.68
7	2018-03-31 0:00:00.000	73870802500001	1310-100	738708	33708	UNISOURCE CANADA	27	25824269.38

etc etc Jan - Dec

What I need is a view that takes this monthly data and creates a single row by account showing all twelve months amounts
i.e.
7 73870802500001 1310-100 738708 33708 Unisource Canada 27 26056794.32 24824338.68 25824269.38 etc to Dec

with my limited skills I would just do a brute force union all for each month, something like this

Select

partitionkey,periodkey,amount as JanAmt,0 AS FebAmt,Account,accountx,entity,entityx,ud1x,RULE_ID
FROM HYP_FDMEE.dbo.TDATASEG
where partitionkey = 7

AND

LEFT(PeriodKey,11) = 'Jan 31 2018'

Union All

Select

partitionkey,periodkey,0 As JanAmt,amount AS FebAmt,Account,accountx,entity,entityx,ud1x,RULE_ID

FROM HYP_FDMEE.dbo.TDATASEG

where

partitionkey = 7
AND LEFT(PeriodKey,11) = 'Feb 28 2018'

etc etc

for all 12 months

then use a second view to group the output of view1

I am hoping there is a much cleaner solution
attached is a word document with example table data

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

randyetheridge SSCommitted Points: 1703 More actions · Answer 1

sorry my server was not available this morning so I could not test my code
this is the correct code

CREATE TABLE dbo.tdataseg

(

Partitionkey VARCHAR(255) NOTNULL,

PeriodKey DATE,

Account VARCHAR(255) NOT NULL,

JDEacct VARCHAR(255) NOT NULL,

Entity VARCHAR(255) NOT NULL,

Entityx VARCHAR(255) NOT NULL,

Udx1 VARCHAR(255) NOTNULL,

Rule_ID VARCHAR(255) NOTNULL,

Amount decimal (29,12)NOT NULL

)

I am working on the data

femi.olaniyan SSC Enthusiast Points: 151 More actions · Answer 2

Try using pivot

select p.Partitionkey ,p.Account ,p.JDEacct ,p.Entity ,p.Entityx ,p.Udx1 ,p.Rule_ID ,January, February, March, April, May, June, July, August, September, October, November, December

from ( select a.partitionkey, a.Account,a.JDEacct ,a. entity,a.entityx,a.Udx1,a.RULE_ID, a.Amount, datename(month,a.PeriodKey) [dt] from #tdataseg a ) c pivot ( max(amount) For dt in (January, February, March, April, May, June, July, August, September, October, November, December) ) p;

randyetheridge SSCommitted Points: 1703 More actions · Answer 3

wow that is awesome. I forgot one item I need it only for partitionkey 7. where would I put the where should I insert the where clause in your statement?

randyetheridge SSCommitted Points: 1703 More actions · Answer 4

select p.Partitionkey

,p.Account

,p.accountx

,p.Entity

,p.Entityx

,p.Ud1x

,p.Rule_ID

,January, February, March, April, May, June, July, August, September, October, November, December

from

(

select a.partitionkey, a.Account,a.ACCOUNTX,a. entity,a.entityx,a.UD1X,a.RULE_ID, a.Amount, datename(month,a.PeriodKey) [dt]

from tdataseg a

where partitionkey = 7 and YEAR(PeriodKey) = '2018'

) c

pivot

(

max(amount)

For dt in (January, February, March, April, May, June, July, August, September, October, November, December)

) p;

I added the Where as noted above and it seems to be working perfect. Thank you so much