[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-12-05
7
Medium Priority
?
246 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
[X]
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
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

656 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