Solved

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

Posted on 2014-12-05
7
236 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.​
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

733 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