Nasty Select Statement ... looking for ideas?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Nasty Select Statement ... looking for ideas??

Author

Topic

ldrenning
Starting Member

23 Posts

Posted - 2008-08-14 : 00:53:07

I was given a file with roughly 75 columns (long story!!!) ... of these columns, 7-10 are used to hold data about a location (address, name, city, etc). The remaining columns are region names that exist across the globe; if a location resides in this region the flag is set to '1'; otherwise it's '0'. All locations exist in 4 regions - no more, no less.

I have dumped this file into SQL Server and am looking to get an output that looks like the following:

Name|Address|City|State|Zip|Region1|Region2|Region3|Region4
Clark|12 Ivy St|Pittsburgh|PA|15232|USA|PA|Pittsburgh|Clark

... to be clear, I need to have the column name in the output of the row. Can anyone give me an idea on how to approach something like this?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-14 : 04:21:29

HI ldrenning,

Easiest thing for us is if you post some sample data and then show us what you need that data to look like after your transformation.

Can you post this (sanitise data if you must)?

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 05:31:46

Use Export Wizard. Export to Flat file. Check the checkbox labelled Column names in the first data row

Madhivanan

Failing to plan is Planning to fail

ldrenning
Starting Member

23 Posts

Posted - 2008-08-14 : 09:31:54

Sure thing; below is an export of the columns and 2 rows of data.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|      ID       |   Property Name    |      Address1      |      Address2      |        City        |       State        |        Zip         |  Ext Property ID   |  Ext Property ID2  |       Sq Ft        |      EP Plan?      |       0 USA        |   1 New England    |        2 CT        |        2 MA        |        2 NH        |        2 RI        | 3 Arlington Center |   3 Atwood Ave     | 3 Braintree Campus | 3 Canonicus Campus |   3 Charles St     |  3 Chestnut Hill   |  3 Clinton Campus  | 3 Codman Sq Campus |   3 Cromwell Sq    | 3 CT - Non-Campus  |   3 Darlington     |   3 Deep River     |  3 Durham Campus   |  3 Elm St Campus   |   3 EPOC Campus    |   3 Exchange PL    | 3 Fairlawn Campus  |  3 Goffstown Br    | 3 Henniker House   |  3 Hyannis Campus  | 3 Jefferson Blvd   | 3 Kenmore Square   |  3 Kent Heights    | 3 Lexington Campus | 3 MA - Non-Campus  | 3 Madison Branch   |  3 Malden Center   | 3 McGregor Street  |     3 Medford      | 3 Milton Village   | 3 Mineral Spring   | 3 Montvale Avenue  | 3 Mount Pleasant   |  3 Nashua Campus   | 3 NH - Non-Campus  |  3 Niantic Branch  |  3 Northeastern    | 3 Norwell- Queen   |   3 Quincy Ave     |  3 Quincy Center   | 3 Randolph Campus  | 3 RI - Non-Campus  |   3 Rocky Hill     |  3 Smith Street    |   3 South Farms    | 3 State St Campus  | 3 Washington Park  | 3 Westbrook Branch |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|             1 | 1 Crawford Road    | 1 Crawford Road    |                    | Westport           | CT                 | 6880               | 3005RBSWestportCra |                    |                  0 | N                  | Yes                | Yes                | Yes                | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | Yes                | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|             2 | 100 Sockanosset    | 100 Sockanossett   |                    | Cranston           | RI                 | 2920               | 30052009662        |                    |             209937 | N                  | Yes                | Yes                | No                 | No                 | No                 | Yes                | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | No                 | Yes                | No                 | No                 | No                 | No                 | No                 | No                 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

My output needs to look like the following:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   Property Name    |      Address1      |      Address2      |        City        |       State        |        Zip         |       Sq Ft        |      Region1       |       Region2       |      Region3       |         Region4         |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 Crawford Road    | 1 Crawford Road    |                    | Westport           | CT                 | 6880               |                  0 | 0 USA              | 1 New England       | 2 CT               | 3 CT - Non-Campus       |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 100 Sockanosset    | 100 Sockanossett   |                    | Cranston           | RI                 | 2920               |             209937 | 0 USA              | 1 New England       | 2 RI               | 3 RI - Non-Campus       |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-14 : 10:40:31

Something like?


SELECT
	[Property Name]
	, [Address1]
	, [Address2]
	, [City]
	, [State]
	, [Zip]
	, [Sq Ft]

	, CASE
		WHEN [0 USA] = 1 THEN '0 USA'
		ELSE NULL
	END AS [Region1]

	, CASE
		WHEN [1 New England] = 1 THEN '1 New England'
		ELSE NULL
	END As [Region2]

	, CASE
		WHEN [2 CT] = 1 THEN '2 CT'
		WHEN [2 MA] = 1 THEN '2 MA'
		WHEN [2 NH] = 1 THEN '2 NH'
		WHEN [2 RI] = 1 THEN '2 RI'
		ELSE NULL
	END AS [Region3]

	-- Repeat CASE above but for your [3 XXXX] column names
FROM
	-- Yourtablename here

Basically use a case statement -- you say that all your entries have a YES in each of the 4 categories.

Give me a shout back if you need this explained in more detail.

Charlie.

-------------
Charlie

ldrenning
Starting Member

23 Posts

Posted - 2008-08-14 : 10:47:13

Thanks Charlie, I'll take a shot at this and let you know what I come up with.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources