How to pivot column in sql server

Question

i'm try to pivot column but it's still error about "Invalid column name"

this is my data in sql server

enter image description here

this is my code

 select EARCBG_Abbrev, EARCIM_Code, EITP_Price, ETAR_Desc
 from EMR_OrderItem
 pivot (count(EITP_Price) for ETAR_Desc in ([OPD], [IPD], [Tourist])) as showpavot
 where EARCIM_Code = '010004'

Can you tell how can i do in this case and tell me why ? Thank you so much.

this is my result when i try your code

enter image description here

possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) — Kevin Hogg, Jun 30 '14 at 12:15

Taryn · Accepted Answer · 2014-06-30T14:45:52.910

1

Since you are using PIVOT, you either need to use subquery to select all of your columns and then your final list includes your "new" pivoted columns similar to this:

select EARCBG_Abbrev,
  EARCIM_Code,
  [OPD], [IPD], [Tourist]
from
(
  select EARCBG_Abbrev, EARCIM_Code, EITP_Price, ETAR_Desc
  from EMR_OrderItem
  where EARCIM_Code = '010004'
) d
pivot 
(
  count(EITP_Price) 
  for ETAR_Desc in ([OPD], [IPD], [Tourist])
) as showpavot;

See SQL Fiddle with Demo

Or you could write the query like this:

select 
  EARCBG_Abbrev,
  EARCIM_Code,
  [OPD], [IPD], [Tourist]
from EMR_OrderItem
pivot 
(
  count(EITP_Price) 
  for ETAR_Desc in ([OPD], [IPD], [Tourist])
) as showpavot
where EARCIM_Code = '010004';

See SQL Fiddle with Demo

In both versions you'll see that the final select list includes the column names that you create during the pivot process.

This could also be written using an aggregate function with a CASE expression:

select
  EARCBG_Abbrev,
  EARCIM_Code,
  sum(case when ETAR_Desc = 'OPD' then EITP_Price else 0 end) OPD,
  sum(case when ETAR_Desc = 'IPD' then EITP_Price else 0 end) IPD,
  sum(case when ETAR_Desc = 'Tourist' then EITP_Price else 0 end) Tourist
from EMR_OrderItem
where EARCIM_Code = '010004'
group by EARCBG_Abbrev, EARCIM_Code

See SQL Fiddle with Demo

edited Jun 30 '14 at 14:45

answered Jun 30 '14 at 12:18

Taryn

242,637
56
362
405

Thank u so much .please check my answer result when i try your code – user3001046 Jun 30 '14 at 13:17
@user3001046 is the value of `EARCBG_Abbrev` exactly the same in all 3 rows? That value is also used in the GROUP BY so if it is not the same you will return multiple rows – Taryn Jun 30 '14 at 13:36
In 3 rows is the same value – user3001046 Jun 30 '14 at 13:49
@user3001046 Can you create a sql fiddle with some sample data? – Taryn Jun 30 '14 at 13:51
i see your demo it is the same solution why the result is not the same. – user3001046 Jun 30 '14 at 14:06
@user3001046 I'm asking you to create a sql fiddle with some of your actual data to see if I can replicate the issue. – Taryn Jun 30 '14 at 14:14
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/56551/discussion-between-bluefeet-and-user3001046). – Taryn Jun 30 '14 at 14:35
then i try to group by EARCBG_Abbrev but it's still error about "Msg 8120, Level 16, State 1, Line 3 Column 'showpavot.EARCIM_Code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. " – user3001046 Jun 30 '14 at 14:35

Damien_The_Unbeliever · Answer 2 · 2014-06-30T12:26:52.537

After you perform a pivot, the first two columns mentioned in the PIVOT (referred to in MSDN docs as the value column and the pivot column) no longer exist in the result set: EITP_Price was aggregated over to construct new values, and ETAR_Desc has become 3 separate columns:

select EARCBG_Abbrev, EARCIM_Code, [OPD], [IPD], [Tourist]
 from EMR_OrderItem
 pivot (count(EITP_Price) for ETAR_Desc in ([OPD], [IPD], [Tourist])) as showpavot
 where EARCIM_Code = '010004'

How to pivot column in sql server

2 Answers2