Formatting Returned Data To Multiple Columns

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

Formatting Returned Data To Multiple Columns

Author

Topic

djpin
Starting Member

27 Posts

Posted - 2014-10-09 : 11:33:03

Hello All,

I need to return a single column (Name.company) but I want the data returned to be viewed in multiple columns.

First is that possible? Second if so how?Third if not then any suggestions?

Hmmm, Ok how about this then...

My data looks like this:

University X
University X - 1
University X - 2
University X - 3
College X
College X - 1
College X - 2
College X - 3
Cmty College X
Cmty College X - 1
Cmty College X - 2
etc.

How can I get it to return only:

University X
College X
Cmty College X

Thanks,

DJ

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-09 : 12:35:44

quote:
I need to return a single column (Name.company) but I want the data returned to be viewed in multiple columns.

I can't understand this. You want it to be a single column and multiple columns? I'm sure you had something specific in mind here but I can't decipher what you intend.

quote:
My data looks like this:

Is this a single column? Some DDL might clarify things for us.

quote:
How can I get it to return only:

If you can rely on the last token being "X" then you could extract only those without a suffix with:

select MyColumn
from MyTable
where RIGHT(MyColumn, 2) = ' X'

It does seem that your table is holding two different types of data, which would be bad, but I really can't tell from the limited info you have displayed here.

No amount of belief makes something a fact. -James Randi

djpin
Starting Member

27 Posts

Posted - 2014-10-09 : 13:10:32

Sorry about that... Ignore the first part of the question I asked please (I confused myself!)

My data looks like this:

CLOSED - DeVry University - Atlanta
CLOSED - DeVry University - Irvine
CLOSED - DeVry University - Lincolnshire
CLOSED - DeVry University - Northeast Phoenix
CLOSED - ECPI College of Technology
CLOSED - ECPI University/Medical Careers Institute - School of Health Science
CLOSED - Education America
CLOSED - Education America - Blairsville Campus
CLOSED - Education America - Topeka -
Closed - Empire Beauty School
CLOSED - Eton Technical Institute
CLOSED - Everest College - Arlington (VA)
CLOSED - Everest College - Chicago
CLOSED - Everest College - Hayward
CLOSED - Everest College - Los Angeles
CLOSED - Everest College - New Orleans
CLOSED - Everest College - San Francisco

I only want it to return one of each that has a similar name:

CLOSED - Everest College
CLOSED - Eton Technical Institute
Closed - Empire Beauty School
CLOSED - Education America
CLOSED - DeVry University
CLOSED - ECPI College of Technology
CLOSED - ECPI University/Medical Careers Institute - School of Health Science

The only thing the data in each group really has in common is the '-'.

I hope this makes more sense.

Thanks,

DJ

FSURob19
Starting Member

5 Posts

Posted - 2014-10-09 : 18:44:08

This code will give you everything to the right of your second dash or the whole string if you only have one. If your data isn't formatted in that way you will need to make changes.

CREATE TABLE #OrginalData (
OrginalData_name nvarchar(200) NOT NULL
);

INSERT INTO #OrginalData
VALUES ('CLOSED - DeVry University - Atlanta');
INSERT INTO #OrginalData
VALUES ('CLOSED - DeVry University - Irvine');
INSERT INTO #OrginalData
VALUES ('CLOSED - DeVry University - Lincolnshire');
INSERT INTO #OrginalData
VALUES ('CLOSED - DeVry University - Northeast Phoenix');
INSERT INTO #OrginalData
VALUES ('CLOSED - ECPI College of Technology');
INSERT INTO #OrginalData
VALUES ('CLOSED - ECPI University/Medical Careers Institute - School of Health Science');
INSERT INTO #OrginalData
VALUES ('CLOSED - Education America');
INSERT INTO #OrginalData
VALUES ('CLOSED - Education America - Blairsville Campus');
INSERT INTO #OrginalData
VALUES ('CLOSED - Education America - Topeka -');
INSERT INTO #OrginalData
VALUES ('Closed - Empire Beauty School');
INSERT INTO #OrginalData
VALUES ('CLOSED - Eton Technical Institute');
INSERT INTO #OrginalData
VALUES ('CLOSED - Everest College - Arlington (VA)');
INSERT INTO #OrginalData
VALUES ('CLOSED - Everest College - Chicago');
INSERT INTO #OrginalData
VALUES ('CLOSED - Everest College - Hayward');
INSERT INTO #OrginalData
VALUES ('CLOSED - Everest College - Los Angeles');
INSERT INTO #OrginalData
VALUES ('CLOSED - Everest College - New Orleans');
INSERT INTO #OrginalData
VALUES ('CLOSED - Everest College - San Francisco');

CREATE TABLE #temp (
col_name nvarchar(200) NOT NULL
);

DECLARE @Col_variable nvarchar(200)
DECLARE @FirstDashLocation int
DECLARE @SecondDashLocation int

DECLARE Col_cursor CURSOR FOR
SELECT DISTINCT OrginalData_name
FROM #OrginalData

OPEN Col_cursor
FETCH NEXT FROM Col_cursor INTO @Col_variable

WHILE @@FETCH_STATUS = 0
BEGIN

Set @FirstDashLocation = CHARINDEX ( '-' , @Col_variable, 0 )
Set @SecondDashLocation = CHARINDEX ( '-' , @Col_variable, @FirstDashLocation + 1)

If @SecondDashLocation <> 0
Set @Col_variable = SUBSTRING ( @Col_variable ,0 , @SecondDashLocation)

INSERT INTO #temp (col_name) VALUES (LTRIM(RTRIM(@Col_variable)));

set @FirstDashLocation = 0
set @SecondDashLocation = 0

FETCH NEXT FROM Col_cursor INTO @Col_variable
END

CLOSE Col_cursor
DEALLOCATE Col_cursor

Select DISTINCT col_name FROM #temp;
DROP TABLE #temp;
DROP TABLE #OrginalData;

djpin
Starting Member

27 Posts

Posted - 2014-10-10 : 08:13:07

This looks promising...

So I would need to do a "insert string for all 1,000 plus companies?"

Can this be shortened in any way?

Thanks!

Thanks,

DJ

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-10 : 11:01:41

Where is your list of companies? In a text file? With a little search and replace, you can convert that text into INSERT statements. really, really simple

djpin
Starting Member

27 Posts

Posted - 2014-10-10 : 11:36:29

I'm pulling straight from the SQL dbase. I'm writing a report and the data is constantly changing so each time it's pulled it'll be refreshed.

Thanks,

DJ

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-10 : 11:38:31

OK -- post the query you are using at the moment.

FSURob19
Starting Member

5 Posts

Posted - 2014-10-10 : 11:43:35

I just did the inserts cause I didn't have your table structure but if you are just pulling from a table, you would just need to point the cursor there:

DECLARE Col_cursor CURSOR FOR
SELECT DISTINCT ColunmWhereDataIs
FROM TableWhereDataIs

Instead of:
DECLARE Col_cursor CURSOR FOR
SELECT DISTINCT OrginalData_name
FROM #OrginalData

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-10 : 11:45:39

What's with the cursor! Totally unnecessary. It'll just slow things down (and make the query longer and harder to read)

FSURob19
Starting Member

5 Posts

Posted - 2014-10-10 : 11:56:04

There are multiple ways, I am just getting back into SQL myself. I gave him one way...you don't like it help him out and you code it.

djpin
Starting Member

27 Posts

Posted - 2014-10-10 : 12:06:45

Really???? I appreciate the assistance from both of you & while I'm not a pro or even a novice - I do know that each programmer has their own style and neither way may be incorrect...

SELECT DISTINCT "Name"."CO_ID", "Name"."MEMBER_TYPE", "Name"."LAST_FIRST", "Name"."COMPANY_RECORD", "Name"."COMPANY", "Name"."COMPANY_SORT"
FROM "Name" "Name"
WHERE "Name"."CO_ID"='209615' AND "Name"."COMPANY" NOT LIKE '%CLOSED%'

Thanks,

DJ

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-10 : 12:23:37

so I assume the name is in the COMPANY column? I mocked up the problem like this:


DECLARE @t TABLE (Company VARCHAR(100))

INSERT INTO @t
VALUES ('CLOSED - DeVry University - Atlanta')
	,('CLOSED - DeVry University - Irvine')
	,('CLOSED - DeVry University - Lincolnshire')
	,('CLOSED - DeVry University - Northeast Phoenix')
	,('CLOSED - ECPI College of Technology')
	,('CLOSED - ECPI University/Medical Careers Institute - School of Health Science')
	,('CLOSED - Education America ')
	,('CLOSED - Education America - Blairsville Campus')
	,('CLOSED - Education America - Topeka - ')
	,('Closed - Empire Beauty School')
	,('CLOSED - Eton Technical Institute')
	,('CLOSED - Everest College - Arlington (VA)')
	,('CLOSED - Everest College - Chicago')
	,('CLOSED - Everest College - Hayward')
	,('CLOSED - Everest College - Los Angeles')
	,('CLOSED - Everest College - New Orleans')
	,('CLOSED - Everest College - San Francisco')

SELECT company
FROM (
	SELECT rtrim(left(company, CASE 
					WHEN second_hyphen.pos > 1
						THEN second_hyphen.pos - 1
					ELSE len(company)
					END))
	FROM @t
	CROSS APPLY (
		SELECT charindex('-', company)
		) first_hyphen(pos)
	CROSS APPLY (
		SELECT charindex('-', company, first_hyphen.pos + 1)
		) second_hyphen(pos)
	) q(company)
GROUP BY company

Is that the sort of thing you're looking for?

djpin
Starting Member

27 Posts

Posted - 2014-10-10 : 12:49:00

Yes, this is exactly what I'm looking for. Greatly appreciate it!

However, as I mentioned previously there are a 1,000+ records and some aren't coded as closed. Do I actually need to code the names in?

Thanks,

DJ

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-10 : 13:15:13

I'm not sure what you mean (that is, I'm not familiar with your data). If the company name does not begin with CLOSED, what does it look like? It should be easy to adjust the query to fit.

djpin
Starting Member

27 Posts

Posted - 2014-10-11 : 18:21:34

A sampling of the 1000+ records looks like:

DeVry University - Atlanta
DeVry University - Irvine
DeVry University - Lincolnshire
CLOSED - DeVry University - Northeast Phoenix
CLOSED - ECPI College of Technology
ECPI University/Medical Careers Institute - School of Health Science
CLOSED - Education America
Education America - Blairsville Campus
Education America - Topeka
Empire Beauty School
Eton Technical Institute
Everest College - Arlington (VA)
Everest College - Chicago
Everest College - Hayward
Everest College - Los Angeles
Everest College - New Orleans
CLOSED - Everest College - San Francisco

I appreciate your patience.

Thanks,

DJ

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-12 : 10:41:55

easy peasy. I just added another condition to the case statement:


	SELECT rtrim(left(company, CASE 
					WHEN LEFT(company, 8) <> 'CLOSED -' AND first_hyphen.pos > 0
					    THEN first_hyphen.pos - 1
					WHEN second_hyphen.pos > 1
						THEN second_hyphen.pos - 1
					ELSE len(company)
					END))

djpin
Starting Member

27 Posts

Posted - 2014-10-14 : 09:18:33

This works for me with the exception of the values - How do I get it to run against the entire table without having to insert ('Everest College - San Francisco') for every record?

Thanks,

DJ

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 10:01:55

Not sure I understand. I ran my modified query against all the input data you provided. I din't have to insert anything else.

e.g. I ran this:


DECLARE @t TABLE (Company VARCHAR(100))

INSERT INTO @t
VALUES ('CLOSED - DeVry University - Atlanta')
	,('CLOSED - DeVry University - Irvine')
	,('CLOSED - DeVry University - Lincolnshire')
	,('CLOSED - DeVry University - Northeast Phoenix')
	,('CLOSED - ECPI College of Technology')
	,('CLOSED - ECPI University/Medical Careers Institute - School of Health Science')
	,('CLOSED - Education America ')
	,('CLOSED - Education America - Blairsville Campus')
	,('CLOSED - Education America - Topeka - ')
	,('Closed - Empire Beauty School')
	,('CLOSED - Eton Technical Institute')
	,('CLOSED - Everest College - Arlington (VA)')
	,('CLOSED - Everest College - Chicago')
	,('CLOSED - Everest College - Hayward')
	,('CLOSED - Everest College - Los Angeles')
    ,('DeVry University - Atlanta')
    ,('DeVry University - Irvine')
    ,('DeVry University - Lincolnshire')
    ,('CLOSED - DeVry University - Northeast Phoenix')
    ,('CLOSED - ECPI College of Technology')
    ,('ECPI University/Medical Careers Institute - School of Health Science')
    ,('CLOSED - Education America')
    ,('Education America - Blairsville Campus')
    ,('Education America - Topeka')
    ,('Empire Beauty School')
    ,('Eton Technical Institute')
    ,('Everest College - Arlington (VA)')
    ,('Everest College - Chicago')
    ,('Everest College - Hayward')
    ,('Everest College - Los Angeles')
    ,('Everest College - New Orleans')
    ,('CLOSED - Everest College - San Francisco')
	,('CLOSED - Everest College - New Orleans')
	,('CLOSED - Everest College - San Francisco')
	

SELECT company
FROM (
	SELECT rtrim(left(company, CASE 
					WHEN LEFT(company, 8) <> 'CLOSED -' AND first_hyphen.pos > 0
					    THEN first_hyphen.pos - 1
					WHEN second_hyphen.pos > 1
						THEN second_hyphen.pos - 1
					ELSE len(company)
					END))
	FROM @t
	CROSS APPLY (
		SELECT charindex('-', company)
		) first_hyphen(pos)
	CROSS APPLY (
		SELECT charindex('-', company, first_hyphen.pos + 1)
		) second_hyphen(pos)
	) q(company)
GROUP BY company

and got this:

quote:

company
CLOSED - DeVry University
CLOSED - ECPI College of Technology
CLOSED - ECPI University/Medical Careers Institute
CLOSED - Education America
Closed - Empire Beauty School
CLOSED - Eton Technical Institute
CLOSED - Everest College
DeVry University
ECPI University/Medical Careers Institute
Education America
Empire Beauty School
Eton Technical Institute
Everest College

Is this not what you want?

djpin
Starting Member

27 Posts

Posted - 2014-10-14 : 10:09:48

Sorry to be a pain. Yes, the query works the way it's supposed to based on the limited data that I gave you. I need to run this again the entire table of several thousand records.

Thanks,

DJ

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 10:33:42

go ahead! Run it! post any incorrect results (with the input rows that cause the incorrect results)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources