Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-12-05
7
234 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
ID: 40484000
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
ID: 40484307
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
ID: 40484885
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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
LVL 7

Expert Comment

by:Phil Davidson
ID: 40485117
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
ID: 40486593
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:
Scott Pletcher earned 500 total points
ID: 40487466
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
ID: 40489982
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Pivot with row total 5 28
sql server query 12 26
fault SQL backup files that wont restore - how common 3 26
SQLCMD Syntax 2 15
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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