Solved

select column from table in MS SQL based on value of an identifier column

Posted on 2014-12-05
7
229 Views
Last Modified: 2014-12-12
This may sound a little convoluted, but I need to know if this is possible. This is a basic example of the data I have now (in a single table) and what I'm trying to select from it:

COLUMNS:
column1, column2, column3, column4, column5

DATA IN THOSE COLUMNS:
column2, a, b, c, d
column3, e, f, g, h
column4, i, j, k, l
column5, m, n, o, p

I want to select the value of the column identified in column1. So a "select ___ from table" would yield this:

RECORDSET:
a
f
k
p

In other words, whatever is in column1, that determines what column I want the data from. In the first row, column1 is "column2", so return "a".

I need the query to put the result in a specific column name, such as "select ___ as columname from table".

If this is possible, I need to figure out the syntax.
Thank you!
0
Comment
Question by:bbdesign
7 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
SELECT	columname =	CASE column1
				WHEN 'column2' THEN column2
				WHEN 'column3' THEN column3
				WHEN 'column4' THEN column4
				WHEN 'column5' THEN column5
			END
FROM	YourTable

Open in new window

0
 
LVL 7

Expert Comment

by:Phil Davidson
Comment Utility
Does this solution need to scale?  Or is the table fixed?  

SQL is best for returning relational rowset data.  The requirements are necessarily at odds with rowsets.

Can you add a new column to the table?  By having a unique integer value, you could write a query with mathematical logic based on it.  The above CASE method may help too.  There is such a thing as an IDENTITY value.  This data type seems very appropriate.

What version of SQL Server are you running?  I ask because newer versions of SQL Server have the ability to select from columns instead of traditional row selections.
0
 

Author Comment

by:bbdesign
Comment Utility
The case statement won't work, there are too many columns, and they change fairly often. So every time we change columns, we would need to remember to change this query.

Does it need to scale? Not exactly sure what that means... we've been using it in its current state for many years, its uses have gradually increased, but we aren't using the table on any large scale (for this purpose, anyway).

Table is not fixed, we make changes to it at least several times a year (add new columns).

I could add new column to the table.

Version is Microsoft SQL Server 2012.

To explain, yes, the way we are doing this is a little goofy, but I'm hoping there is a workaround because the data already exists and is being used by many other scripts in its current form. The column names refer to model numbers of products. We add a new model number when a new product comes out. The rows refer to parts, and each part can be used for multiple products.

I'm wondering if the names of the columns (that refer to model numbers) could be stored separately in a lookup table, would that help? I just need to say something like:

select ____ as modelnum from table where ___

Thanks for looking!
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 7

Expert Comment

by:Phil Davidson
Comment Utility
You may want to create a separate table with a foreign key constraint.  This may help with crafting logic.  You may want to add an additional column.  That the data changes frequently represents some complexity.  Can you try a separate table with a foreign key constraint?
0
 

Author Comment

by:bbdesign
Comment Utility
I honestly don't know what a Foreign Key Constraint is. I'm willing to give it a try. Can you explain how that would tie Column1 to the correct "other" column to pull the data from?

Thanks.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
You could generate an inline-table-function to do the CASE conversion.  You can bind the function to the schema so that you would be forced to drop the function and re-create it when the table schema (definition) changed.

Here's sample code to generate the CASE conditions based on the current column names in the table:


DECLARE @table_name sysname
DECLARE @column_conversions varchar(max)
DECLARE @sql varchar(max)
SET @table_name = 'dbo.table_name'


SELECT @column_conversions = (
    SELECT ' WHEN ''' + CAST(c.name AS varchar(128)) + ''' THEN CAST([' + CAST(c.name AS varchar(128)) + '] AS varchar(100))'
    FROM sys.columns c
    WHERE
        c.object_id = OBJECT_ID(@table_name) AND
        c.name NOT IN ('column1')
    ORDER BY column_id
    FOR XML PATH('')
    )

SELECT @sql = '
SELECT CASE [column1] ' + @column_conversions + ' END AS result
FROM ' + CAST(@table_name AS varchar(128))

PRINT @sql
0
 

Author Comment

by:bbdesign
Comment Utility
Thank you for that. Unfortunately, its a bit over my head. I'm sending this query via an ASP script to MS SQL to return a recordset, I don't think I have the option of sending all that extra SQL code. Maybe I should be more specific. Here is my base query:

select distinct
	tbl_masterparts.po_model
	tbl_masterparts.partno
from
	tbl_order_parts, tbl_order_parts_cust, tbl_masterparts
where
	tbl_order_parts.ordnum=tbl_order_parts_cust.ordnum and
	tbl_order_parts.partno=tbl_masterparts.partno and
	tbl_order_parts_cust.status='2'
order by
	tbl_masterparts.partno

Open in new window


That gives me the two values I need, po_model and partno. This is a second query I am running:

select <po_model value here> as dianum from tbl_masterparts where partno='<partno value here>'

Open in new window


What I am really after is a way to fold that second query into the first, so everything happens at once. The part of this I am stuck on is this:

select <po_model value here> as dianum

Open in new window


The value of a specific column in the record set is actually the column name I am trying to return as "dianum".

If this is too complicated to make into one query, I can always leave it as two separate ones, I am just looking to gain some efficiency.
Thank you!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now