Help with Data Format

Question

Help with Data Format

copling

Say Hey Kid

Points: 696
More actions
March 13, 2014 at 8:52 am

#305081

Hello, I was wondering if someone can help me with an issue that I'm having. The problem is that I'm trying to format my query in the following format and get the data that I need.
A_ID A_TR_DATE A_REF_NOJRPJFS
---------------- ----------- ---- ----
152547*12/5/2011 J478 -350.00
155257*22/6/2011 J488 150.00
155257*33/2/2011 45-54 100.00
I only want to show sources such as a JR, PJ and FS where there is a JR value (whether its neg. or pos.) and the substring of the first 6 characters in A_ID match. So, in this particular case I should have 2 records for JR and 1 PJ record because they have matching accounts in the first 6 digits. Below is the some source code. Thanks.
CREATE TABLE [dbo].[A_TABLE](
[A_ID] [varchar](40) NOT NULL,
[A_SOURCE] [varchar](10) NULL,
[A_REF_NO] [varchar](20) NULL,
[A_DEBIT] [decimal](13, 2) NULL,
[A_CREDIT] [decimal](13, 2) NULL,
[A_TR_DATE] [datetime] NULL
CONSTRAINT [PK_A_TABLE] PRIMARY KEY CLUSTERED
(
[A_ID] ASC
))
GO
INSERT INTO A_TABLE SELECT '205545*1','PJ','45-55',25.00,NULL,'03/01/2011' UNION
SELECT '152547*1','JR','J478',NULL,350.00,'02/05/2011' UNION
SELECT '152547*3','PJ','45-54',NULL,100.00,'03/02/2011' UNION
SELECT '815369*1','FS','F87-7',120.00,NULL,'02/06/2011' UNION
SELECT '152547*2','JR','J488',150.00,NULL,'02/06/2011'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

I would start with normalizing your tables appropriately. That would make this a lot easier to do. This should get you what you need:CREATE TABLE [dbo].[A_TABLE](

[A_ID] [varchar](40) NOT NULL,

[A_SOURCE] [varchar](10) NULL,

[A_REF_NO] [varchar](20) NULL,

[A_DEBIT] [decimal](13, 2) NULL,

[A_CREDIT] [decimal](13, 2) NULL,

[A_TR_DATE] [datetime] NULL

CONSTRAINT [PK_A_TABLE] PRIMARY KEY CLUSTERED

(

[A_ID] ASC

))

GO

INSERT INTO A_TABLE SELECT '205545*1','PJ','45-55',25.00,NULL,'03/01/2011' UNION

SELECT '152547*1','JR','J478',NULL,350.00,'02/05/2011' UNION

SELECT '152547*3','PJ','45-54',NULL,100.00,'03/02/2011' UNION

SELECT '815369*1','FS','F87-7',120.00,NULL,'02/06/2011' UNION

SELECT '152547*2','JR','J488',150.00,NULL,'02/06/2011';

with JRValues as

(

SELECT left(A_ID,6) as A_ID

FROM A_TABLE

where A_SOURCE = 'JR')

select a.*

from A_Table a

where left(a.A_ID,6) in (

select A_ID from JRValues)

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

copling Say Hey Kid Points: 696 More actions · Answer 2

Thanks for the solution. However, I was wondering is there a way that I can get the data to where the SOURCE shows up as a column and have their values under them?

For example

A_ID A_SOURCE A_REF_NO JR PJ FS

Do you think a pivot table with due?

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 3

Building on Keith's solution:

WITHJRValues AS

(

SELECT LEFT(A_ID,6) AS A_ID

FROM A_TABLE

WHEREA_SOURCE = 'JR'

)

SELECTA_ID,

A_TR_DATE,

A_REF_NO,

CASE WHEN A_SOURCE = 'JR' THEN ISNULL(-a_debit,a_credit) ELSE NULL END AS 'JR',

CASE WHEN A_SOURCE = 'PJ' THEN ISNULL(-a_debit,a_credit) ELSE NULL END AS 'PJ',

CASE WHEN A_SOURCE = 'FS' THEN ISNULL(-a_debit,a_credit) ELSE NULL END AS 'FS'

FROMA_Table a

WHERELEFT (a.A_ID,6) IN (

SELECT A_ID FROM JRValues);

copling Say Hey Kid Points: 696 More actions · Answer 4

Thank you Mr. Watson for your query. That's what I needed.