I need to change the column headings to start with a letter what would be the best way to do this.

Question

I need to change the column headings to start with a letter what would be the best way to do this.

kat35601

SSC Eights!

Points: 825
More actions
July 25, 2016 at 7:58 am

#328376

I need to change the column headings to start with a letter what would be the best way to do this.The heading are 2013,2014,2015,2016 I would like for them to start with a letter. x2013,x2014,x2015,x2016 something like that.
select
*
from
(SELECT
datepart(WK,ompOrderDate) as week_
,ompOrderSubtotalBase
,DATEPART(yyyy,ompOrderDate) as year_
FROM m1_kf.dbo.SalesOrders
WHERE ompClosed = - 1 and ompCustomerOrganizationID is not null
and
(
ompOrderDate >= '01-01-2016'
AND ompOrderDate <= '12-31-2016'
)
OR
(
ompOrderDate >= convert(char(11),DATEADD(yy, -1, '01-01-2016'),101)
AND ompOrderDate <= convert(char(11),DATEADD(yy, -1,'12-31-2016'),101)
)
OR (
ompOrderDate >= convert(char(11),DATEADD(yy, -2, '01-01-2016'),101)
AND ompOrderDate <= convert(char(11),DATEADD(yy, -2, '12-31-2016'),101)
)
OR (
ompOrderDate >= convert(char(11),DATEADD(yy, -3, '01-01-2016'),101)
AND ompOrderDate <= convert(char(11),DATEADD(yy, -3, '12-31-2016'),101)
)
) as src
pivot
(
sum(ompOrderSubtotalBase)
for year_ in ([2013], [2014], [2015] , [2016])
) piv
order by
week_

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Yes, that's why we have column alias. Remove the star(*) and explicitly list the columns and add alias as needed.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kat35601 SSC Eights! Points: 825 More actions · Answer 2

I tried this but it did not work. the column does change to x2013 but the data rows for that column become 2013 etc

select

week_,2013 as x2013,2014 as x2014, 2015 as x2015, 2016 as x2016

from

(SELECT

datepart(WK,ompOrderDate) as week_

,ompOrderSubtotalBase

,DATEPART(yyyy,ompOrderDate) as year_

FROM m1_kf.dbo.SalesOrders

WHERE ompClosed = - 1 and ompCustomerOrganizationID is not null

and

(

ompOrderDate >= '01-01-2016'

AND ompOrderDate <= '12-31-2016'

)

OR

(

ompOrderDate >= convert(char(11),DATEADD(yy, -1, '01-01-2016'),101)

AND ompOrderDate <= convert(char(11),DATEADD(yy, -1,'12-31-2016'),101)

)

OR (

ompOrderDate >= convert(char(11),DATEADD(yy, -2, '01-01-2016'),101)

AND ompOrderDate <= convert(char(11),DATEADD(yy, -2, '12-31-2016'),101)

)

OR (

ompOrderDate >= convert(char(11),DATEADD(yy, -3, '01-01-2016'),101)

AND ompOrderDate <= convert(char(11),DATEADD(yy, -3, '12-31-2016'),101)

)

) as src

pivot

(

sum(ompOrderSubtotalBase)

for year_ in ([2013], [2014], [2015] , [2016])

) piv

order by

wee

x SSC-Insane Points: 23660 More actions · Answer 3

kat35601 (7/25/2016)
I tried this but it did not work. the column does change to x2013 but the data rows for that column become 2013 etc
select
week_,2013 as x2013,2014 as x2014, 2015 as x2015, 2016 as x2016
from
(SELECT
datepart(WK,ompOrderDate) as week_
,ompOrderSubtotalBase
,DATEPART(yyyy,ompOrderDate) as year_
FROM m1_kf.dbo.SalesOrders
WHERE ompClosed = - 1 and ompCustomerOrganizationID is not null
and
(
ompOrderDate >= '01-01-2016'
AND ompOrderDate <= '12-31-2016'
)
OR
(
ompOrderDate >= convert(char(11),DATEADD(yy, -1, '01-01-2016'),101)
AND ompOrderDate <= convert(char(11),DATEADD(yy, -1,'12-31-2016'),101)
)
OR (
ompOrderDate >= convert(char(11),DATEADD(yy, -2, '01-01-2016'),101)
AND ompOrderDate <= convert(char(11),DATEADD(yy, -2, '12-31-2016'),101)
)
OR (
ompOrderDate >= convert(char(11),DATEADD(yy, -3, '01-01-2016'),101)
AND ompOrderDate <= convert(char(11),DATEADD(yy, -3, '12-31-2016'),101)
)
) as src
pivot
(
sum(ompOrderSubtotalBase)
for year_ in ([2013], [2014], [2015] , [2016])
) piv
order by
wee

try enclosing the column names up top in brackets too? like [2013], [2014] etc?

kat35601 SSC Eights! Points: 825 More actions · Answer 4

kat35601

SSC Eights!

Points: 825

July 25, 2016 at 8:29 am

#1891819

[Brackets] worked Thanks

x SSC-Insane Points: 23660 More actions · Answer 5

x

SSC-Insane

Points: 23660

July 25, 2016 at 12:08 pm

#1891869

awesome!