Pull data from one Column for a report

Question

Pull data from one Column for a report

abarefoot@abatix.com

SSC Rookie

Points: 37
More actions
October 2, 2008 at 4:00 pm

#193634

I have the following column.
Jurisdiction_id
TX
TXSAN
TXSANCOR
I'm wanting to create a report that looks like this:
State State_County State_County_City
TX TXSAN TXSANCOR
I'm not having any luck with subqueries. Any help will be appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Alex Barefoot (10/2/2008)
I'm wanting to create a report that looks like this:
State State_County State_County_City
TX TXSAN TXSANCOR
I'm not having any luck with subqueries. Any help will be appreciated.

This will give you the result you need...

SELECT 'TX' as [State], 'TXSAN' as [State_County], 'TXSANCOR' as [State_County_City]

... but if you want to get something that will really help you, please post your table structure, sample data and explain how to identify whether any particular row is a State, County or City. Also, it would be fine to post what you tried and didn't work.

Please read this to understand what I'm asking: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

abarefoot@abatix.com SSC Rookie Points: 37 More actions · Answer 2

Sorry I'm new to this and not even sure if the below statement is correct.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

-===== Create the test table with

CREATE TABLE #mytable

(jurisdiction_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table

INSERT INTO #mytable

(jurisdiction_id)

SELECT 'CA', UNION ALL

SELECT 'NVSTOGLD', UNION ALL

SELECT 'TXDAL', UNION ALL

SELECT 'FL', UNION ALL

SELECT 'CALAXVEE', UNION ALL

SELECT 'NV', UNION ALL

SELECT 'CALAX', UNION ALL

SELECT 'FLDUVARL', UNION ALL

SELECT 'FLJAC', UNION ALL

SELECT 'FLDUVARL', UNION ALL

SELECT 'NVEUR', UNION ALL

SELECT 'OUTOFSTATE', UNION ALL

SELECT 'NOTAX', UNION ALL

SELECT 'TX',

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #mytable OFF

At first I used LEN to filter by count. But this is only good if I want State only. Ineed State, State/County and State/County/City each in their own column. I even thought I could create an Alias for multiple Jurisdiction_id entries but that didn't work. I was going to try using subqueries but I'm not sure how.

SELECT jurisdiction_id

FROM tax_jurisdiction

GROUP BY jurisdiction_id

HAVING (LEN(jurisdiction_id) = 2)

Thanks for the help!

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

There's nothing there to relate state to county, etc, etc. Are the other columns in that table that might be used to do that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

abarefoot@abatix.com SSC Rookie Points: 37 More actions · Answer 4

No that's why I thought I could use LEN since State = 2 Characters, State/County = 5 Characters, State/County/City = 8 Characters. I know it would need a few exceptions for NoTax and OutofState entries.

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 5

Heh... it's customary to test your own test code before you post it to make sure it works. Anyway, since there seems to be a pattern to things...

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(jurisdiction_id VARCHAR(15))

--===== Insert the test data into the test table

INSERT INTO #mytable

(jurisdiction_id)

SELECT 'CA' UNION ALL

SELECT 'NVSTOGLD' UNION ALL

SELECT 'TXDAL' UNION ALL

SELECT 'FL' UNION ALL

SELECT 'CALAXVEE' UNION ALL

SELECT 'NV' UNION ALL

SELECT 'CALAX' UNION ALL

SELECT 'FLDUVARL' UNION ALL

SELECT 'FLJAC' UNION ALL

SELECT 'FLDUVARL' UNION ALL

SELECT 'NVEUR' UNION ALL

SELECT 'OUTOFSTATE' UNION ALL

SELECT 'NOTAX' UNION ALL

SELECT 'TX'

--===== This is one solution but not like your request

SELECT LEFT(Jurisdiction_ID,2) AS State,

SUBSTRING(Jurisdiction_ID,3,3) AS County,

RIGHT(Jurisdiction_ID,3) AS City

FROM #MyTable

WHERE LEN(Jurisdiction_ID) = 8

AND Jurisdiction_ID NOT IN ('NoTax','OutOfState')

ORDER BY State, County, City

--===== This solution is like your request

SELECT LEFT(Jurisdiction_ID,2) AS State,

LEFT(Jurisdiction_ID,5) AS State_County,

LEFT(Jurisdiction_ID,8) AS State_County_City

FROM #MyTable

WHERE LEN(Jurisdiction_ID) = 8

AND Jurisdiction_ID NOT IN ('NoTax','OutOfState')

ORDER BY State, State_County, State_County_City

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

abarefoot@abatix.com SSC Rookie Points: 37 More actions · Answer 6

--===== This solution is like your request

SELECT LEFT(Jurisdiction_ID,2) AS State,

LEFT(Jurisdiction_ID,5) AS State_County,

LEFT(Jurisdiction_ID,8) AS State_County_City

FROM #MyTable

WHERE LEN(Jurisdiction_ID) = 8

AND Jurisdiction_ID NOT IN ('NoTax','OutOfState')

ORDER BY State, State_County, State_County_City

[/code][/quote]

Thanks for the help. That's what I was wanting.

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 7

You bet. Thanks for the feedback, Alex. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)