I'm trying to do a partial pivot...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I'm trying to do a partial pivot...

Author

Topic

midnitefistfite
Starting Member

2 Posts

Posted - 2011-12-05 : 18:27:53

Hey everyone,

I'm trying to turn my columns into rows for a portion of my data set and I'm writing the query in Access using SQL view. Here is the way my data is formatted right now (referred to as "Billing" in my query)...

http://i.imgur.com/uViXz.jpg

...and I want to get it looking like this (referred to as "ARBillingTemplate" in my query), with an additional fourth column labeled "Value" that represents the number pulled off of each of the seven columns in "Billing".

http://i.imgur.com/XH3B5.jpg

I'm a n00b with SQL and I've been told a pivot is ideal for this kind of thing. Here's what I tried to do initially...

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

INSERT INTO TEST

SELECT ARBillingTemplate.Customer,
ARBillingTemplate.Invoice,
ARBillingTemplate.Type,

iif((Billing.Customer = ARBillingTemplate.Customer AND Billing.Invoice = ARBillingTemplate.Invoice),
Billing.('ARBillingTemplate.Type'),NULL) AS Value

FROM ARBillingTemplate, Billing;

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

I don't think the Billing.('ARBillingTemplate.Type') statement is valid -- I keep getting syntax errors. Is there an alternative way of writing this block? To me, it logically makes sense.

Any help is appreciated. Thanks!

consultor.dwh
Starting Member

6 Posts

Posted - 2011-12-05 : 18:41:17

Unpivot operator is an option. But your data must be in a table in SQL Server 2005 or superior. You can read abou PIVOT and UNPIVOT operators here:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Another option is create one query for each of your columns an later join all this queries like this:

select ARBillingTemplate.Customer
,ARBillingTemplate.Invoice
,ARBillingTemplate.Type
,[0 to 30] invoice
FROM ARBillingTemplate, Billing
union all
select ARBillingTemplate.Customer
,ARBillingTemplate.Invoice
,ARBillingTemplate.Type
,[31 to 60] invoice
FROM ARBillingTemplate, Billing
union all
select ARBillingTemplate.Customer
,ARBillingTemplate.Invoice
,ARBillingTemplate.Type
,[61 to 90] invoice
FROM ARBillingTemplate, Billing
union all
select ARBillingTemplate.Customer
,ARBillingTemplate.Invoice
,ARBillingTemplate.Type
,[91] invoice
FROM ARBillingTemplate, Billing

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 00:09:38

are you asking for sql server query or access sql query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

midnitefistfite
Starting Member

2 Posts

Posted - 2011-12-06 : 10:38:22

quote:
Originally posted by visakh16

are you asking for sql server query or access sql query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This query is being done in MS Access, and I'm using the "SQL View" to write my query, rather than "Design View" for example. I didn't think there was a big difference between SQL Server and Access SQL. I apologize if I posted this in the wrong section.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 10:41:56

quote:
Originally posted by midnitefistfite

quote:
Originally posted by visakh16

are you asking for sql server query or access sql query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This query is being done in MS Access, and I'm using the "SQL View" to write my query, rather than "Design View" for example. I didn't think there was a big difference between SQL Server and Access SQL. I apologize if I posted this in the wrong section.

there are quite a few differences. so you may be better off posting this in access forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources