Re: SQL Syntax Help: Crosstab/Transform/Pivot?
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 16 Dec 2006 23:02:26 +0100
On Fri, 15 Dec 2006 12:02:01 -0800, KitCaz wrote:
of FTP file names (from/to) and how I'd like to reference them in variable
names, for each server:
ftpServer ftpVarName ftpFrom ftpTo
SERVER CTC_ALV CTC_ALV.txt IDT.CTC.CLINTRAC.ALV
SERVER CTC_CLM CTC_CLM.txt IDT.CTC.CLINTRAC.CLM
SERVER CTC_CLS CTC_CLSF.txt IDT.CTC.CLINTRAC.CLSF
SERVER2 CTC_USR CTC_USERS.txt IDP.CTC.CLINTRAC.USERS
SERVER2 CTC_VIS1 CTC_VISIT.txt IDP.CTC.CLINTRAC.VISIT
SERVER2 CTC_VIS2 CTC_VISIT_DEL.txt IDP.CTC.CLINTRAC.VISITDEL
etc.
I want to cross-tab/transform/pivot (I'm not sure the right term) so that
the variable name field becomes a column header and the file name becomes the
row content for the selected server.
Having been an MS Access head for a while, I know this is easily done with
an MS Access crosstab wizard, and the results look like this, which is what
I'm after (with many more columns than showing here, I just truncated the
results so it'd fit into this message window):
ftpServer CTC_ALV CTC_CHARGE_PROC CTC_CHRG_PROC_CDM...
SERVER CTC_ALV.txt CTC_CHARGE_PROC.txt...
etc.
When I display that MS Access query definition as a SQL statement, it
displays as the statement here:
TRANSFORM Max(MSK_IDB_Extract_FTP_Files.ftpFrom) AS MaxOfftpFrom
SELECT MSK_IDB_Extract_FTP_Files.ftpServer
FROM MSK_IDB_Extract_FTP_Files
WHERE (((MSK_IDB_Extract_FTP_Files.ftpServer)="SERVER"))
GROUP BY MSK_IDB_Extract_FTP_Files.ftpServer
PIVOT MSK_IDB_Extract_FTP_Files.ftpVarName;
I can't figure how to rephrase this on MS SQL terms (or even if it's
possible). Can you? I've searched the online books for 'pivot', 'transform'
and 'crosstab' but the results are meager. Is the functionality at work
below only available in MS Access, or is there a way to achieve this in MS
SQL that I just can't seem to find?
Hi Chris,
You can do this, but you need to know the number of columns and their
names beforehand. In SQL Server 2005, you can do this using the new
PIVOT operator:
SELECT ftpServer, [CTC_ALV], [CTC_CLM], [CTC_CLS], ....
FROM (SELECT ftpServer, ftpVarName, ftpFrom
FROM MSK_IDB_Extract_FTP_Files) AS p
PIVOT (MAX(ftpFrom)
FOR ftpVarName IN ( [CTC_ALV], [CTC_CLM], [CTC_CLS], ....)
) AS pvt;
In all versions of SQL Server, you can use GROUP BY and CASE to achieve
the same result:
SELECT ftpServer,
MAX(CASE WHEN ftpVarName ='CTC_ALV' THEN ftpFrom END) AS CTC_ALV,
MAX(CASE WHEN ftpVarName ='CTC_CLM' THEN ftpFrom END) AS CTC_CLM,
MAX(CASE WHEN ftpVarName ='CTC_CLS' THEN ftpFrom END) AS CTC_CLS,
...
FROM MSK_IDB_Extract_FTP_Files
GROUP BY ftpServer;
--
Hugo Kornelis, SQL Server MVP
.
- Prev by Date: Re: Search for a character in a column
- Next by Date: SQL Query statement (Sum problem)
- Previous by thread: Re: Search for a character in a column
- Next by thread: SQL Query statement (Sum problem)
- Index(es):
Relevant Pages
|
|
