?
Solved

How to create a RESULT SET with Unique Account records based on records with Duplicate Account Values ?

Posted on 2009-04-28
1
Medium Priority
?
206 Views
Last Modified: 2013-12-05
I am developing an Access application using Access as the front end and SQL Server 2000 as the back end database.

How would I modify the following SQL Statement to create a result set with Unique Account values ?

The field UDL_Date is a field with a value representing the Month End Date and for the output, I want a separate column for each UDL_Date value per Account.

The Attached file represents 1 Account value for 6 records. In the RESULT SET I would like to display 1 record representing these 6 duplicate Account values.
Select 
Account,
case when year(UDL_date) = 2008 and month(UDL_Date) = 1  then max(UDL_Date) else null end AS JAN08,
case when year(UDL_date) = 2008 and month(UDL_Date) = 2  then max(UDL_Date) else null end AS FEB08,
case when year(UDL_date) = 2008 and month(UDL_Date) = 3  then max(UDL_Date) else null end AS MAR08,
case when year(UDL_date) = 2008 and month(UDL_Date) = 4  then max(UDL_Date) else null end AS APR08,
case when year(UDL_date) = 2008 and month(UDL_Date) = 6  then max(UDL_Date) else null end AS JUN08,
case when year(UDL_date) = 2008 and month(UDL_Date) = 9  then max(UDL_Date) else null end AS SEP08,
C_TAPS_ACCOUNT,
C_LNAME,
C_NAME_ADDR_TYPE,
C_ADDRESS1,
C_ADDRESS2,
C_ADDRESS3,
C_ADDRESS4
From dbo.tblLexDSolo
group by account, 
C_TAPS_ACCOUNT,
C_LNAME,
C_NAME_ADDR_TYPE,
C_ADDRESS1,
C_ADDRESS2,
C_ADDRESS3,
C_ADDRESS4,
year(UDL_date),
month(UDL_Date)

Open in new window

PWM.XLS
0
Comment
Question by:zimmer9
1 Comment
 
LVL 22

Accepted Solution

by:
dportas earned 2000 total points
ID: 24264985
SELECT
Account,
MAX(CASE WHEN YEAR(UDL_date) = 2008 and month(UDL_Date) = 1  THEN UDL_Date ELSE NULL END) AS JAN08,
MAX(CASE WHEN YEAR(UDL_date) = 2008 and month(UDL_Date) = 2  THEN UDL_Date ELSE NULL END) AS FEB08,
MAX(CASE WHEN YEAR(UDL_date) = 2008 and month(UDL_Date) = 3  THEN UDL_Date ELSE NULL END) AS MAR08,
MAX(CASE WHEN YEAR(UDL_date) = 2008 and month(UDL_Date) = 4  THEN UDL_Date ELSE NULL END) AS APR08,
MAX(CASE WHEN YEAR(UDL_date) = 2008 and month(UDL_Date) = 6  THEN UDL_Date ELSE NULL END) AS JUN08,
MAX(CASE WHEN YEAR(UDL_date) = 2008 and month(UDL_Date) = 9  THEN UDL_Date ELSE NULL END) AS SEP08,
C_TAPS_ACCOUNT,
C_LNAME,
C_NAME_ADDR_TYPE,
C_ADDRESS1,
C_ADDRESS2,
C_ADDRESS3,
C_ADDRESS4
FROM dbo.tblLexDSolo
GROUP BY account,
C_TAPS_ACCOUNT,
C_LNAME,
C_NAME_ADDR_TYPE,
C_ADDRESS1,
C_ADDRESS2,
C_ADDRESS3,
C_ADDRESS4 ;
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question