Query Assistance Combining Columns into one

I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column.

My Query:

Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID
ORDER BY Product_ID DESC

ISNULL(small_text_value,large_text_value) AS TheValue|||

This would return large_Text when small_text is null. but notice if small_text is null you will get 2 columns with same value. If that is not what you want please post back with more details.

SELECTEAV.PRODUCT_ID,CASEWHEN EAV.SMALL_TEXT_VALUEISNULLTHEN EAV.LARGE_TEXT_VALUEELSE EAV.SMALL_TEXT_VALUEEND , EAV.LARGE_TEXT_VALUEFROMEXTENDED_ATTRIBUTE_VALUES EAVINNERJOIN EXTENDED_ATTRIBUTES EAWHERE EAV.Ext_Att_ID = EA.Ext_Att_IDORDER BY Product_IDDESC

|||

Or

COALESCE(small_text_value,large_text_value) AS TheValue

If the small is null, the data will be from the large column.

|||

Everyones solutions worked perfect. Thanks!

I've now got something else I need to do. To build this info, I need to pull from a few different tables.

I now have the query that we just worked on, and I set it equal to the column of data I need:

Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, ISNULL(small_text_value,large_text_value) AS TheValue, EXTENDED_ATTRIBUTES.Column_Name
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID And
EXTENDED_ATTRIBUTES.Column_Name = '4 Ball EP'
ORDER BY Product_ID DESC

And I now also need the rows from this query:

Select PRODUCT_FEATURE_VALUES.PRODUCT_ID, SHARED_FEATURE_VALUES.Feature_Text_Value As TheValue
From PRODUCT_FEATURE_VALUES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES
Where PRODUCT_FEATURE_VALUES.Feature_Type_ID = SHARED_FEATURE_TYPES.Feature_Type_ID And
SHARED_FEATURE_TYPES.Feature_Type = '4 Ball EP' And
PRODUCT_FEATURE_VALUES.Feature_Value_ID = SHARED_FEATURE_VALUES.Feature_Value_ID

I'd like them to both return in one query if thats possible...

|||If the columns are the same I think you could use a Union statement.|||

I used a Union and that works. I now have:

Select
PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID,
SHARED_FEATURE_VALUES.Feature_Text_Value As TheValue,
SHARED_FEATURE_TYPES.Feature_Type AS ColumnName
From PRODUCT_FEATURE_VALUES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES
Where
PRODUCT_FEATURE_VALUES.Feature_Type_ID = SHARED_FEATURE_TYPES.Feature_Type_ID And
PRODUCT_FEATURE_VALUES.Feature_Value_ID = SHARED_FEATURE_VALUES.Feature_Value_ID
UNION

Select
EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID,
ISNULL(small_text_value,large_text_value) AS TheValue,
EXTENDED_ATTRIBUTES.Column_Name AS ColumnName
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID
ORDER BY Product_ID DESC

How can I display the values in a column as a column. In the 2 fields above that I'm declaring as ColumnName, there are alot of individual values. How can I make one of those values the columnname, and if I want another and so on... I'm trying to build an app for some internal querying and if the user says I want to see all values for A and B, then I would want to display A and B as individual columns, even though they are just a value in the same column of data in the database.

|||

SELECT t1.ProductID,MAX(CASE WHEN Column_Name='A' THEN TheValue ELSE NULL END) AS A, MAX(CASE WHEN ColumnName='B' THEN TheValue ELSE NULL END) AS B

FROM ({Your giant query here}) AS t1

GROUP BY t1.ProductID

or for SQL 2005, you can use the new PIVOT stuff.

SELECT ProductID,A,B

FROM
({Your giant query here}) t1
PIVOT
(
MAX(TheValue)
FOR ColumnName IN
('A','B')
) AS pvt
ORDER BY ProductID

|||

I keep getting Invalid column name 'Column_Name'.

Do I need to set the column name of A and B to the clumns I want displayed?

SELECT t1.ProductID,MAX(CASE WHEN Column_Name='A' THEN TheValue ELSE NULL END) AS A, MAX(CASE WHEN ColumnName='B' THEN TheValue ELSE NULL END) AS B

FROM (Select
PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID,
SHARED_FEATURE_VALUES.Feature_Text_Value As TheValue,
SHARED_FEATURE_TYPES.Feature_Type AS ColumnName
From PRODUCT_FEATURE_VALUES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES
Where
PRODUCT_FEATURE_VALUES.Feature_Type_ID = SHARED_FEATURE_TYPES.Feature_Type_ID And
PRODUCT_FEATURE_VALUES.Feature_Value_ID = SHARED_FEATURE_VALUES.Feature_Value_ID
UNION

Select
EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID,
ISNULL(small_text_value,large_text_value) AS TheValue,
EXTENDED_ATTRIBUTES.Column_Name AS ColumnName
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID) AS t1

GROUP BY t1.ProductID

|||

I got this now and it works good for getting the columns I need. Can I add conditioning for each column after the ColumnName='Value I want as a Column'? Like if I want Test2 As a column, but also only show there Test2 <> 5, where do I add that?

SELECT
t1.ProductID,
MAX(CASE WHEN ColumnName='test1' THEN TheValue ELSE NULL END) AS A,
MAX(CASE WHEN ColumnName='test2' THEN TheValue ELSE NULL END) AS B

FROM (Select
PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID,
SHARED_FEATURE_VALUES.Feature_Text_Value As TheValue,
SHARED_FEATURE_TYPES.Feature_Type AS ColumnName
From PRODUCT_FEATURE_VALUES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES
Where
PRODUCT_FEATURE_VALUES.Feature_Type_ID = SHARED_FEATURE_TYPES.Feature_Type_ID And
PRODUCT_FEATURE_VALUES.Feature_Value_ID = SHARED_FEATURE_VALUES.Feature_Value_ID
UNION

Select
EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID,
ISNULL(small_text_value,large_text_value) AS TheValue,
EXTENDED_ATTRIBUTES.Column_Name AS ColumnName
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID) AS t1

GROUP BY t1.ProductID

|||

I need some more assistance with this since some requirements have changed. Sometimes there will be multiple records with the same columnname, but a distinct value. No matter what I do, its only returning 1 record for each column. I'd like to return all records for each column name and combine them then into one. I dont know if this is possible... In the case base, Military Specification Number is actually in the table 3 times, but this query only grabs the last record of the 3...

SELECT

TOP(100)PERCENT PRODUCT_NUMBER, PRODUCT_NAME,MAX(CASEWHEN ColumnName='Military Specification Number'THEN TheValueELSENULLEND)AS [Military Specification Number]

FROM

(SELECT dbo.PRODUCT_FEATURE_VALUES.PRODUCT_IDAS ProductID, dbo.SHARED_FEATURE_VALUES.FEATURE_TEXT_VALUEAS TheValue,

dbo

.SHARED_FEATURE_TYPES.FEATURE_TYPEAS ColumnName, dbo.PRODUCTS.PRODUCT_NUMBER,

dbo

.PRODUCTS.PRODUCT_NAMEFROM dbo.PRODUCT_FEATURE_VALUESINNERJOIN

dbo

.SHARED_FEATURE_TYPESON

dbo

.PRODUCT_FEATURE_VALUES.FEATURE_TYPE_ID= dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE_IDINNERJOIN

dbo

.SHARED_FEATURE_VALUESON

dbo

.PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID= dbo.SHARED_FEATURE_VALUES.FEATURE_VALUE_IDINNERJOIN

dbo

.PRODUCTSON dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID= dbo.PRODUCTS.PRODUCT_IDUNIONALLSELECT dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_IDAS ProductID,ISNULL(dbo.EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE,

dbo

.EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE)AS TheValue, dbo.EXTENDED_ATTRIBUTES.COLUMN_NAMEAS ColumnName,

PRODUCTS_1

.PRODUCT_NUMBER, PRODUCTS_1.PRODUCT_NAMEFROM dbo.EXTENDED_ATTRIBUTE_VALUESINNERJOIN

dbo

.EXTENDED_ATTRIBUTESON

dbo

.EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID= dbo.EXTENDED_ATTRIBUTES.EXT_ATT_IDINNERJOIN

dbo

.PRODUCTSAS PRODUCTS_1ON dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID= PRODUCTS_1.PRODUCT_ID)AS t1

WHERE

PRODUCT_NUMBER='05048'

GROUP

BY PRODUCT_NUMBER, PRODUCT_NAME

ORDER

BY PRODUCT_NUMBER