asked on

Pivot Table in SQL Server

I have a table HF01A which has 3 columns
Acct_Nbr string
Col_Head string (has 3 values HF_C, HF_D, HF_L)
Col_Value integer

Example

ABC        HF_C     5
ABC        HF_L     7
ABC        HF_D    3
DEF        HF_L     2

Open in new window

I need a table
Acct_Nbr string
HF_C integer (NULL allowed)
HF_D integer (NULL allowed)
HF_L integer (NULL allowed)

Acct_Nbr           HF_C         HF_D       HF_L
ABC                  5            3          7
DEF                                          2

Open in new window

mlmcc

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

ASKER

Will this put the data into the table or can I use it as part of a query?

mlmcc

ste5an

Depends on your needs.. It does not contain an INSERT, so no, it does not put the data into the table. You can use the the entire query as subquery.

Mike McCracken

ASKER

This is what worked. Need [ ] around the values

SELECT  P.Acct_Nbr ,
        P.HF_L ,
        P.HF_D ,
        P.HF_C
FROM    @HF01A HA PIVOT ( SUM(HA.Col_Value) FOR HA.Col_Head IN ( [HF_C], [HF_D], [HF_L] ) ) P

Open in new window

Thanks

mlmcc

Mike McCracken

ASKER

Worked with a minor modification

ste5an

hmm, what SQL Server version? Cause the names are canoncial and should work without the brackets..

Mike McCracken

ASKER

We didn't try it without the []. We had another issue with it and when I check the syntax on MSDN it said the [ ] were required. SInce there ie only one line per account and type I eliminated the SUM which is also required.

I think it is SQL Server 2012

mlmcc

ste5an

Brackets are only needed, if the object name contains special chars like the hyphen or space etc. The SUM() is not needed, but an aggregate is mandatory. Cause the PIVOT cannot know, that there is only one value. But even if there is only one, SUM() is the choice, cause integers are additive. Would the values be strings, then you would chose MIN() or MAX().