rename field on the fly

Question

Post reply

rename field on the fly

m_belhocine

SSC Journeyman

Points: 90
More actions
October 8, 2009 at 9:15 am

#142403

My fragment code ...
drop table #test2
Select * into #test2 from (
SELECT #test.CODE_GESTIONNAIRE, pac_.campagne, pac_.inscrit
FROM #test LEFT JOIN pac_ ON #test.CODE_GESTIONNAIRE = pac_.code_gestionnaire
GROUP BY #test.CODE_GESTIONNAIRE, pac_.campagne, pac_.inscrit ) src2
PIVOT (max(inscrit) FOR campagne
IN ([20100009],[20100010])) AS pvt2
select * from #test2
|CODE_GESTIONNAIRE | 20100009 |210100010|
+-------------------------------------+-----------------+-----------------+
...
...
...
I Want to dynamic rename my field (20100009 and 20100010) on (S_210100009 ans S_210100010)
thanks for your help

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, the quick and easy fix would be to alias from the final select instead of using select *

SELECT

CODE_GESTIONNAIRE

[20100009] As S_20100009,

[210100010] As S_20100010

From #test2

is that what you were looking for?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

since you need to know the values to pivot in order to pivot, you can't do it dynamically, but you can alias the values.

but you can certainly alias them like this:

SELECT #test.CODE_GESTIONNAIRE,

[20100009] As S_20100009,

[20100010] AS S_20100010,

pac_.campagne, pac_.inscrit

FROM #test LEFT JOIN pac_ ON #test.CODE_GESTIONNAIRE = pac_.code_gestionnaire

GROUP BY #test.CODE_GESTIONNAIRE, pac_.campagne, pac_.inscrit ) src2

PIVOT (max(inscrit) FOR campagne

IN ([20100009],[20100010])) AS pvt2

you didn't provide the real CREATE/INSERT/full code to test, so here is a working example where values are aliased for you to use as a model:

CREATE TABLE #EXAMPLE(

Subject VARCHAR(30),

Date datetime,

Hour int,

DataA decimal(18,8),

DataB decimal(18,8) )

INSERT INTO #EXAMPLE

SELECT 'aaa','1/1/2009', 0 , 1212,1234 UNION ALL

SELECT 'aaa','1/1/2009', 1 , 234,3242 UNION ALL

SELECT 'aaa','1/1/2009', 2 , 344,5435 UNION ALL

SELECT 'aaa','1/1/2009', 3 , 122,7504 UNION ALL

SELECT 'aaa','1/1/2009', 4 , -100,9605 UNION ALL

SELECT 'aaa','1/1/2009', 5 , -322,11705 UNION ALL

SELECT 'aaa','1/1/2009', 6 , -544,13806 UNION ALL

SELECT 'aaa','1/1/2009', 7 , -766,15906 UNION ALL

SELECT 'aaa','1/1/2009', 8 , -988,18007 UNION ALL

SELECT 'aaa','1/1/2009', 9 ,-1210,20107 UNION ALL

SELECT 'aaa','1/1/2009', 10,-1432,22208 UNION ALL

SELECT 'aaa','1/1/2009', 11,-1654,24308 UNION ALL

SELECT 'aaa','1/1/2009', 12,-1876,26409 UNION ALL

SELECT 'aaa','1/1/2009', 13,-2098,28509 UNION ALL

SELECT 'aaa','1/1/2009', 14,-2320,30610 UNION ALL

SELECT 'aaa','1/1/2009', 15,-2542,32710 UNION ALL

SELECT 'aaa','1/1/2009', 16,-2764,34811 UNION ALL

SELECT 'aaa','1/1/2009', 17,-2986,36911 UNION ALL

SELECT 'aaa','1/1/2009', 18,-3208,39012 UNION ALL

SELECT 'aaa','1/1/2009', 19,-3430,41112 UNION ALL

SELECT 'aaa','1/1/2009', 20,-3652,43213 UNION ALL

SELECT 'aaa','1/1/2009', 21,-3874,45313 UNION ALL

SELECT 'aaa','1/1/2009', 22,-4096,47414 UNION ALL

SELECT 'aaa','1/1/2009', 23,-4318,49514 UNION ALL

SELECT 'aaa','1/2/2009', 0 , 1212,1234 UNION ALL

SELECT 'aaa','1/2/2009', 1 , 234,3242 UNION ALL

SELECT 'aaa','1/2/2009', 2 , 344,5435 UNION ALL

SELECT 'aaa','1/2/2009', 3 , 122,7504 UNION ALL

SELECT 'aaa','1/2/2009', 4 , -100,9605 UNION ALL

SELECT 'aaa','1/2/2009', 5 , -322,11705 UNION ALL

SELECT 'aaa','1/2/2009', 6 , -544,13806 UNION ALL

SELECT 'aaa','1/2/2009', 7 , -766,15906 UNION ALL

SELECT 'aaa','1/2/2009', 8 , -988,18007 UNION ALL

SELECT 'aaa','1/2/2009', 9 ,-1210,20107 UNION ALL

SELECT 'aaa','1/2/2009', 10,-1432,22208 UNION ALL

SELECT 'aaa','1/2/2009', 11,-1654,24308 UNION ALL

SELECT 'aaa','1/2/2009', 12,-1876,26409 UNION ALL

SELECT 'aaa','1/2/2009', 13,-2098,28509 UNION ALL

SELECT 'aaa','1/2/2009', 14,-2320,30610 UNION ALL

SELECT 'aaa','1/2/2009', 15,-2542,32710 UNION ALL

SELECT 'aaa','1/2/2009', 16,-2764,34811 UNION ALL

SELECT 'aaa','1/2/2009', 17,-2986,36911 UNION ALL

SELECT 'aaa','1/2/2009', 18,-3208,39012 UNION ALL

SELECT 'aaa','1/2/2009', 19,-3430,41112 UNION ALL

SELECT 'aaa','1/2/2009', 20,-3652,43213 UNION ALL

SELECT 'aaa','1/2/2009', 21,-3874,45313 UNION ALL

SELECT 'aaa','1/2/2009', 22,-4096,47414 UNION ALL

SELECT 'aaa','1/2/2009', 23,-4318,49514 UNION ALL

SELECT 'bbb','1/1/2009', 0 , 1212,1234 UNION ALL

SELECT 'bbb','1/1/2009', 1 , 234,3242 UNION ALL

SELECT 'bbb','1/1/2009', 2 , 344,5435 UNION ALL

SELECT 'bbb','1/1/2009', 3 , 122,7504 UNION ALL

SELECT 'bbb','1/1/2009', 4 , -100,9605 UNION ALL

SELECT 'bbb','1/1/2009', 5 , -322,11705 UNION ALL

SELECT 'bbb','1/1/2009', 6 , -544,13806 UNION ALL

SELECT 'bbb','1/1/2009', 7 , -766,15906 UNION ALL

SELECT 'bbb','1/1/2009', 8 , -988,18007 UNION ALL

SELECT 'bbb','1/1/2009', 9 ,-1210,20107 UNION ALL

SELECT 'bbb','1/1/2009', 10,-1432,22208 UNION ALL

SELECT 'bbb','1/1/2009', 11,-1654,24308 UNION ALL

SELECT 'bbb','1/1/2009', 12,-1876,26409 UNION ALL

SELECT 'bbb','1/1/2009', 13,-2098,28509 UNION ALL

SELECT 'bbb','1/1/2009', 14,-2320,30610 UNION ALL

SELECT 'bbb','1/1/2009', 15,-2542,32710 UNION ALL

SELECT 'bbb','1/1/2009', 16,-2764,34811 UNION ALL

SELECT 'bbb','1/1/2009', 17,-2986,36911 UNION ALL

SELECT 'bbb','1/1/2009', 18,-3208,39012 UNION ALL

SELECT 'bbb','1/1/2009', 19,-3430,41112 UNION ALL

SELECT 'bbb','1/1/2009', 20,-3652,43213 UNION ALL

SELECT 'bbb','1/1/2009', 21,-3874,45313 UNION ALL

SELECT 'bbb','1/1/2009', 22,-4096,47414 UNION ALL

SELECT 'bbb','1/1/2009', 23,-4318,49514 UNION ALL

SELECT 'bbb','1/2/2009', 0 , 1212,1234 UNION ALL

SELECT 'bbb','1/2/2009', 1 , 234,3242 UNION ALL

SELECT 'bbb','1/2/2009', 2 , 344,5435 UNION ALL

SELECT 'bbb','1/2/2009', 3 , 122,7504 UNION ALL

SELECT 'bbb','1/2/2009', 4 , -100,9605 UNION ALL

SELECT 'bbb','1/2/2009', 5 , -322,11705 UNION ALL

SELECT 'bbb','1/2/2009', 6 , -544,13806 UNION ALL

SELECT 'bbb','1/2/2009', 7 , -766,15906 UNION ALL

SELECT 'bbb','1/2/2009', 8 , -988,18007 UNION ALL

SELECT 'bbb','1/2/2009', 9 ,-1210,20107 UNION ALL

SELECT 'bbb','1/2/2009', 10,-1432,22208 UNION ALL

SELECT 'bbb','1/2/2009', 11,-1654,24308 UNION ALL

SELECT 'bbb','1/2/2009', 12,-1876,26409 UNION ALL

SELECT 'bbb','1/2/2009', 13,-2098,28509 UNION ALL

SELECT 'bbb','1/2/2009', 14,-2320,30610 UNION ALL

SELECT 'bbb','1/2/2009', 15,-2542,32710 UNION ALL

SELECT 'bbb','1/2/2009', 16,-2764,34811 UNION ALL

SELECT 'bbb','1/2/2009', 17,-2986,36911 UNION ALL

SELECT 'bbb','1/2/2009', 18,-3208,39012 UNION ALL

SELECT 'bbb','1/2/2009', 19,-3430,41112 UNION ALL

SELECT 'bbb','1/2/2009', 20,-3652,43213 UNION ALL

SELECT 'bbb','1/2/2009', 21,-3874,45313 UNION ALL

SELECT 'bbb','1/2/2009', 22,-4096,47414 UNION ALL

SELECT 'bbb','1/2/2009', 23,-4318,49514

SELECT [Subject],[Date],[Hour],DataA,DataB FROM #EXAMPLE

--------------

SELECT

[Subject],

[Date],

--value as description

[0] AS [DA00],[1] AS [DA01],[2] AS [DA02],[3] AS [DA03],[4] AS [DA04],[5] AS [DA05],

[6] AS [DA06],[7] AS [DA07],[8] AS [DA08],[9] AS [DA09],[10] AS [DA10],[11] AS [DA11],

[12] AS [DA12],[13] AS [DA13],[14] AS [DA14],[15] AS [DA15],[16] AS [DA16],[17] AS [DA17],

[18] AS [DA18],[19] AS [DA19],[20] AS [DA20],[21] AS [DA21],[22] AS [DA22],[23] AS [DA23]

FROM

(SELECT [Subject],[Date],[Hour],DataA FROM #EXAMPLE)

AS TheSource

PIVOT

(

MIN(DataA) --the value to grab that matches the HOUR

FOR

[HOUR] --each value in HOUR

IN ( [0], [1], [2], [3], [4], [5],

[6], [7], [8], [9],[10],[11],

[12],[13],[14],[15],[16],[17],

[18],[19],[20],[21],[22],[23])

) AS PIVOTALIAS

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!