Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
... and so on).
Just wondering.
Thank you!
Kyra
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
Kyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA
|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:

>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>
|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:

> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
>

query help

I have a name column that contains both first and last
names:
Col1
John Doe
I'd like to split it into two columns, a first and
lastname:
firstname lastname
-- --
John Doe
Anyone have any easy way to do this?
Do you *always* have two words, separated by a space? I.e., what does your data look like?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
> I have a name column that contains both first and last
> names:
> Col1
> --
> John Doe
> I'd like to split it into two columns, a first and
> lastname:
> firstname lastname
> -- --
> John Doe
> Anyone have any easy way to do this?
|||For the most part. There are some names that contain a
middle inital..
the data looks like this:
John Doe
Jane Doe
George W Bush
Bill Clinton
John Kerry
Jim Bob Smith
etc....
I'm not overly concerned with getting everything perfect.
To be honest, I'd be cool with just the first names.
>--Original Message--
>Do you *always* have two words, separated by a space?
I.e., what does your data look like?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
>
>.
>
|||This should get you started:
CREATE TABLE Presidents (
FullName varchar(50)
)
GO
INSERT INTO Frog VALUES ('George W Bush')
INSERT INTO Frog VALUES ('Bill Clinton')
INSERT INTO Frog VALUES ('Ronald Reagan')
INSERT INTO Frog VALUES ('George H Bush')
INSERT INTO Frog VALUES ('Gerald Ford')
INSERT INTO Frog VALUES ('Richard Nixon')
GO
SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
CASE
WHEN PATINDEX('% _ %', FullName) > 0
THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1, 1)
ELSE ''
END AS 'MI',
RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last Name'
FROM Presidents
You can look up the various pieces used.
CHARINDEX
PATINDEX
SUBSTRING
REVERSE
CASE
Rick Sawtell
MCT, MCSD, MCDBA
|||Ummm. Change the INSERT INTO commands to reflect the Presidents table...
Sorry bout that.
Rick
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> This should get you started:
> CREATE TABLE Presidents (
> FullName varchar(50)
> )
> GO
> INSERT INTO Frog VALUES ('George W Bush')
> INSERT INTO Frog VALUES ('Bill Clinton')
> INSERT INTO Frog VALUES ('Ronald Reagan')
> INSERT INTO Frog VALUES ('George H Bush')
> INSERT INTO Frog VALUES ('Gerald Ford')
> INSERT INTO Frog VALUES ('Richard Nixon')
> GO
>
> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
> CASE
> WHEN PATINDEX('% _ %', FullName) > 0
> THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1,
1)
> ELSE ''
> END AS 'MI',
> RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last
Name'
> FROM Presidents
>
> You can look up the various pieces used.
> CHARINDEX
> PATINDEX
> SUBSTRING
> REVERSE
> CASE
> Rick Sawtell
> MCT, MCSD, MCDBA
>
|||Cool, that did it. One other thing though... Could the
same be used for an address column? I used the same
syntax, but ran into an issue...
The column has a street address:
123 N. Main St.
I used the SQL and pulled the house number, directional,
and suffix, but lost the street name. Any help?
Thanks!

>--Original Message--
>Ummm. Change the INSERT INTO commands to reflect the
Presidents table...[vbcol=seagreen]
>Sorry bout that.
>
>Rick
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
AS 'First Name',[vbcol=seagreen]
(' ', FullName) +1,[vbcol=seagreen]
>1)
(FullName)) - 1) AS 'Last
>Name'
>
>.
>
|||Ummm..
Use the SUBSTRING function to get everything to the right of your
directional. Then apply the same CHARINDEX or PATINDEX functions to the
return value you are looking for from the return value of the SUBSTRING
function.
On a separate note... SQL really isn't the best choice to be doing
procedural language things like this.
If you dumped everything to a text file and used VBScript, you could
probably get this thing hashed out more quickly.
Rick
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:c45a01c47a48$9c9aef50$a301280a@.phx.gbl...[vbcol=seagreen]
> Cool, that did it. One other thing though... Could the
> same be used for an address column? I used the same
> syntax, but ran into an issue...
> The column has a street address:
> 123 N. Main St.
> I used the SQL and pulled the house number, directional,
> and suffix, but lost the street name. Any help?
> Thanks!
> Presidents table...
> AS 'First Name',
> (' ', FullName) +1,
> (FullName)) - 1) AS 'Last

Query help

I need to update 6 column on 1 table depending on 2 other
columns on another table (All 8 columns exists on both
tables)
here is what I am trying to write and it gives me error:
Update Table1
Set start_date = (Select start_date from Table2),
end_date = (Select end_date from Table2),
user1 = (Select user1 from Table2),
user2 = (Select user2 from Table2),
user3 = (Select user3 from Table2),
user4 = (Select user4 from Table2)
FROM Table2
Where Table2.project = Table1.project AND
Table2.Pjt_entity = Table1.Pjt_entity
Thanks for any help.
Hi,
Try this,
Update Table1
Set a.start_date = b.start_date ,
a.end_date = b.end_date,
a.user1 = b.user1,
a.user2 = b.user2,
a.user3 = b.user3,
a.user4 = b.user4
FROM Table1 a,Table2 b
Where a.project = b.project
AND a..Pjt_entity = b.Pjt_entity
Tahnks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2001c486d0$5a97bdb0$a301280a@.phx.gbl...
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
|||Todd wrote:
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
Well if you have a 1:1 between the tables, you just need to specify the
column to update:
Update Table1
Set start_date = b.start_date,
end_date = b.end_date,
etc...
FROM Table2 b
Where b.project = Table1.project AND
b.Pjt_entity = Table1.Pjt_entity
David G.
|||David G. wrote:
> Todd wrote:
> Well if you have a 1:1 between the tables, you just need to specify
> the column to update:
> Update Table1
> Set start_date = b.start_date,
> end_date = b.end_date,
> etc...
> FROM Table2 b
> Where b.project = Table1.project AND
> b.Pjt_entity = Table1.Pjt_entity
Left off a table in the FROM clause. See Hari's post instead.
David G.

Query help

I need to update 6 column on 1 table depending on 2 other
columns on another table (All 8 columns exists on both
tables)
here is what I am trying to write and it gives me error:
Update Table1
Set start_date = (Select start_date from Table2),
end_date = (Select end_date from Table2),
user1 = (Select user1 from Table2),
user2 = (Select user2 from Table2),
user3 = (Select user3 from Table2),
user4 = (Select user4 from Table2)
FROM Table2
Where Table2.project = Table1.project AND
Table2.Pjt_entity = Table1.Pjt_entity
Thanks for any help.Hi,
Try this,
Update Table1
Set a.start_date = b.start_date ,
a.end_date = b.end_date,
a.user1 = b.user1,
a.user2 = b.user2,
a.user3 = b.user3,
a.user4 = b.user4
FROM Table1 a,Table2 b
Where a.project = b.project
AND a..Pjt_entity = b.Pjt_entity
Tahnks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2001c486d0$5a97bdb0$a301280a@.phx.gbl...
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.|||Todd wrote:
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
Well if you have a 1:1 between the tables, you just need to specify the
column to update:
Update Table1
Set start_date = b.start_date,
end_date = b.end_date,
etc...
FROM Table2 b
Where b.project = Table1.project AND
b.Pjt_entity = Table1.Pjt_entity
David G.|||David G. wrote:
> Todd wrote:
> Well if you have a 1:1 between the tables, you just need to specify
> the column to update:
> Update Table1
> Set start_date = b.start_date,
> end_date = b.end_date,
> etc...
> FROM Table2 b
> Where b.project = Table1.project AND
> b.Pjt_entity = Table1.Pjt_entity
Left off a table in the FROM clause. See Hari's post instead.
David G.

Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
.. and so on).
Just wondering.
Thank you!
Kyra
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBAKyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:

>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:

> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
>
>sql

query help

I have a name column that contains both first and last
names:
Col1
--
John Doe
I'd like to split it into two columns, a first and
lastname:
firstname lastname
-- --
John Doe
Anyone have any easy way to do this?Do you *always* have two words, separated by a space? I.e., what does your d
ata look like?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:c07e01c47a31$4e2ec290$a601
280a@.phx.gbl...
> I have a name column that contains both first and last
> names:
> Col1
> --
> John Doe
> I'd like to split it into two columns, a first and
> lastname:
> firstname lastname
> -- --
> John Doe
> Anyone have any easy way to do this?|||For the most part. There are some names that contain a
middle inital..
the data looks like this:
John Doe
Jane Doe
George W Bush
Bill Clinton
John Kerry
Jim Bob Smith
etc....
I'm not overly concerned with getting everything perfect.
To be honest, I'd be cool with just the first names.
>--Original Message--
>Do you *always* have two words, separated by a space?
I.e., what does your data look like?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
>
>.
>|||This should get you started:
CREATE TABLE Presidents (
FullName varchar(50)
)
GO
INSERT INTO Frog VALUES ('George W Bush')
INSERT INTO Frog VALUES ('Bill Clinton')
INSERT INTO Frog VALUES ('Ronald Reagan')
INSERT INTO Frog VALUES ('George H Bush')
INSERT INTO Frog VALUES ('Gerald Ford')
INSERT INTO Frog VALUES ('Richard Nixon')
GO
SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
CASE
WHEN PATINDEX('% _ %', FullName) > 0
THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1, 1)
ELSE ''
END AS 'MI',
RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last Name'
FROM Presidents
You can look up the various pieces used.
CHARINDEX
PATINDEX
SUBSTRING
REVERSE
CASE
Rick Sawtell
MCT, MCSD, MCDBA|||Ummm. Change the INSERT INTO commands to reflect the Presidents table...
Sorry bout that.
Rick
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> This should get you started:
> CREATE TABLE Presidents (
> FullName varchar(50)
> )
> GO
> INSERT INTO Frog VALUES ('George W Bush')
> INSERT INTO Frog VALUES ('Bill Clinton')
> INSERT INTO Frog VALUES ('Ronald Reagan')
> INSERT INTO Frog VALUES ('George H Bush')
> INSERT INTO Frog VALUES ('Gerald Ford')
> INSERT INTO Frog VALUES ('Richard Nixon')
> GO
>
> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
> CASE
> WHEN PATINDEX('% _ %', FullName) > 0
> THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1,
1)
> ELSE ''
> END AS 'MI',
> RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last
Name'
> FROM Presidents
>
> You can look up the various pieces used.
> CHARINDEX
> PATINDEX
> SUBSTRING
> REVERSE
> CASE
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Cool, that did it. One other thing though... Could the
same be used for an address column? I used the same
syntax, but ran into an issue...
The column has a street address:
123 N. Main St.
I used the SQL and pulled the house number, directional,
and suffix, but lost the street name. Any help?
Thanks!

>--Original Message--
>Ummm. Change the INSERT INTO commands to reflect the
Presidents table...
>Sorry bout that.
>
>Rick
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
AS 'First Name',[vbcol=seagreen]
(' ', FullName) +1,[vbcol=seagreen]
>1)
(FullName)) - 1) AS 'Last[vbcol=seagreen]
>Name'
>
>.
>|||Ummm..
Use the SUBSTRING function to get everything to the right of your
directional. Then apply the same CHARINDEX or PATINDEX functions to the
return value you are looking for from the return value of the SUBSTRING
function.
On a separate note... SQL really isn't the best choice to be doing
procedural language things like this.
If you dumped everything to a text file and used VBScript, you could
probably get this thing hashed out more quickly.
Rick
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:c45a01c47a48$9c9aef50$a301280a@.phx.gbl...[vbcol=seagreen]
> Cool, that did it. One other thing though... Could the
> same be used for an address column? I used the same
> syntax, but ran into an issue...
> The column has a street address:
> 123 N. Main St.
> I used the SQL and pulled the house number, directional,
> and suffix, but lost the street name. Any help?
> Thanks!
>
> Presidents table...
> AS 'First Name',
> (' ', FullName) +1,
> (FullName)) - 1) AS 'Last

Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
.. and so on).
Just wondering.
Thank you!
Kyra
--
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBAKyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:
>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:
> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
> >Help, please!
> >
> >I have 3 columns with numeric values. i want to write a query that returns
> >the following:
> >If all 3 columns are populated, then the middle value (not the average);
> >If one colunn has a null, then the lesser value,
> >If 2 are null, then the remaining value.
> >
> >For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> >the result to be "10". If ColumnB were null, then the result would be "5".
> >Making sense?
> >
> >Is there a way to do this, without having to create a bazillion Case When
> >statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
> >else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
> >.. and so on).
> >
> >Just wondering.
> >Thank you!
> >Kyra
> >
> >
> >
>

Query help

I need to update 6 column on 1 table depending on 2 other
columns on another table (All 8 columns exists on both
tables)
here is what I am trying to write and it gives me error:
Update Table1
Set start_date = (Select start_date from Table2),
end_date = (Select end_date from Table2),
user1 = (Select user1 from Table2),
user2 = (Select user2 from Table2),
user3 = (Select user3 from Table2),
user4 = (Select user4 from Table2)
FROM Table2
Where Table2.project = Table1.project AND
Table2.Pjt_entity = Table1.Pjt_entity
Thanks for any help.Hi,
Try this,
Update Table1
Set a.start_date = b.start_date ,
a.end_date = b.end_date,
a.user1 = b.user1,
a.user2 = b.user2,
a.user3 = b.user3,
a.user4 = b.user4
FROM Table1 a,Table2 b
Where a.project = b.project
AND a..Pjt_entity = b.Pjt_entity
Tahnks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2001c486d0$5a97bdb0$a301280a@.phx.gbl...
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.|||Todd wrote:
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
Well if you have a 1:1 between the tables, you just need to specify the
column to update:
Update Table1
Set start_date = b.start_date,
end_date = b.end_date,
etc...
FROM Table2 b
Where b.project = Table1.project AND
b.Pjt_entity = Table1.Pjt_entity
David G.|||David G. wrote:
> Todd wrote:
>> I need to update 6 column on 1 table depending on 2 other
>> columns on another table (All 8 columns exists on both
>> tables)
>> here is what I am trying to write and it gives me error:
>> Update Table1
>> Set start_date = (Select start_date from Table2),
>> end_date = (Select end_date from Table2),
>> user1 = (Select user1 from Table2),
>> user2 = (Select user2 from Table2),
>> user3 = (Select user3 from Table2),
>> user4 = (Select user4 from Table2)
>> FROM Table2
>> Where Table2.project = Table1.project AND
>> Table2.Pjt_entity = Table1.Pjt_entity
>> Thanks for any help.
> Well if you have a 1:1 between the tables, you just need to specify
> the column to update:
> Update Table1
> Set start_date = b.start_date,
> end_date = b.end_date,
> etc...
> FROM Table2 b
> Where b.project = Table1.project AND
> b.Pjt_entity = Table1.Pjt_entity
Left off a table in the FROM clause. See Hari's post instead.
--
David G.

query help

I have a name column that contains both first and last
names:
Col1
--
John Doe
I'd like to split it into two columns, a first and
lastname:
firstname lastname
-- --
John Doe
Anyone have any easy way to do this?Do you *always* have two words, separated by a space? I.e., what does your data look like?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
> I have a name column that contains both first and last
> names:
> Col1
> --
> John Doe
> I'd like to split it into two columns, a first and
> lastname:
> firstname lastname
> -- --
> John Doe
> Anyone have any easy way to do this?|||For the most part. There are some names that contain a
middle inital..
the data looks like this:
John Doe
Jane Doe
George W Bush
Bill Clinton
John Kerry
Jim Bob Smith
etc....
I'm not overly concerned with getting everything perfect.
To be honest, I'd be cool with just the first names.
>--Original Message--
>Do you *always* have two words, separated by a space?
I.e., what does your data look like?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
>> I have a name column that contains both first and last
>> names:
>> Col1
>> --
>> John Doe
>> I'd like to split it into two columns, a first and
>> lastname:
>> firstname lastname
>> -- --
>> John Doe
>> Anyone have any easy way to do this?
>
>.
>|||This should get you started:
CREATE TABLE Presidents (
FullName varchar(50)
)
GO
INSERT INTO Frog VALUES ('George W Bush')
INSERT INTO Frog VALUES ('Bill Clinton')
INSERT INTO Frog VALUES ('Ronald Reagan')
INSERT INTO Frog VALUES ('George H Bush')
INSERT INTO Frog VALUES ('Gerald Ford')
INSERT INTO Frog VALUES ('Richard Nixon')
GO
SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
CASE
WHEN PATINDEX('% _ %', FullName) > 0
THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1, 1)
ELSE ''
END AS 'MI',
RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last Name'
FROM Presidents
You can look up the various pieces used.
CHARINDEX
PATINDEX
SUBSTRING
REVERSE
CASE
Rick Sawtell
MCT, MCSD, MCDBA|||Ummm. Change the INSERT INTO commands to reflect the Presidents table...
Sorry bout that.
Rick
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> This should get you started:
> CREATE TABLE Presidents (
> FullName varchar(50)
> )
> GO
> INSERT INTO Frog VALUES ('George W Bush')
> INSERT INTO Frog VALUES ('Bill Clinton')
> INSERT INTO Frog VALUES ('Ronald Reagan')
> INSERT INTO Frog VALUES ('George H Bush')
> INSERT INTO Frog VALUES ('Gerald Ford')
> INSERT INTO Frog VALUES ('Richard Nixon')
> GO
>
> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
> CASE
> WHEN PATINDEX('% _ %', FullName) > 0
> THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1,
1)
> ELSE ''
> END AS 'MI',
> RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last
Name'
> FROM Presidents
>
> You can look up the various pieces used.
> CHARINDEX
> PATINDEX
> SUBSTRING
> REVERSE
> CASE
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Cool, that did it. One other thing though... Could the
same be used for an address column? I used the same
syntax, but ran into an issue...
The column has a street address:
123 N. Main St.
I used the SQL and pulled the house number, directional,
and suffix, but lost the street name. Any help?
Thanks!
>--Original Message--
>Ummm. Change the INSERT INTO commands to reflect the
Presidents table...
>Sorry bout that.
>
>Rick
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
>> This should get you started:
>> CREATE TABLE Presidents (
>> FullName varchar(50)
>> )
>> GO
>> INSERT INTO Frog VALUES ('George W Bush')
>> INSERT INTO Frog VALUES ('Bill Clinton')
>> INSERT INTO Frog VALUES ('Ronald Reagan')
>> INSERT INTO Frog VALUES ('George H Bush')
>> INSERT INTO Frog VALUES ('Gerald Ford')
>> INSERT INTO Frog VALUES ('Richard Nixon')
>> GO
>>
>> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1)
AS 'First Name',
>> CASE
>> WHEN PATINDEX('% _ %', FullName) > 0
>> THEN SUBSTRING(FullName, CHARINDEX
(' ', FullName) +1,
>1)
>> ELSE ''
>> END AS 'MI',
>> RIGHT(FullName, CHARINDEX(' ', REVERSE
(FullName)) - 1) AS 'Last
>Name'
>> FROM Presidents
>>
>> You can look up the various pieces used.
>> CHARINDEX
>> PATINDEX
>> SUBSTRING
>> REVERSE
>> CASE
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>
>.
>|||Ummm..
Use the SUBSTRING function to get everything to the right of your
directional. Then apply the same CHARINDEX or PATINDEX functions to the
return value you are looking for from the return value of the SUBSTRING
function.
On a separate note... SQL really isn't the best choice to be doing
procedural language things like this.
If you dumped everything to a text file and used VBScript, you could
probably get this thing hashed out more quickly.
Rick
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:c45a01c47a48$9c9aef50$a301280a@.phx.gbl...
> Cool, that did it. One other thing though... Could the
> same be used for an address column? I used the same
> syntax, but ran into an issue...
> The column has a street address:
> 123 N. Main St.
> I used the SQL and pulled the house number, directional,
> and suffix, but lost the street name. Any help?
> Thanks!
> >--Original Message--
> >Ummm. Change the INSERT INTO commands to reflect the
> Presidents table...
> >
> >Sorry bout that.
> >
> >
> >Rick
> >
> >
> >
> >"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> >news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> >> This should get you started:
> >>
> >> CREATE TABLE Presidents (
> >> FullName varchar(50)
> >> )
> >>
> >> GO
> >>
> >> INSERT INTO Frog VALUES ('George W Bush')
> >> INSERT INTO Frog VALUES ('Bill Clinton')
> >> INSERT INTO Frog VALUES ('Ronald Reagan')
> >> INSERT INTO Frog VALUES ('George H Bush')
> >> INSERT INTO Frog VALUES ('Gerald Ford')
> >> INSERT INTO Frog VALUES ('Richard Nixon')
> >>
> >> GO
> >>
> >>
> >> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1)
> AS 'First Name',
> >> CASE
> >> WHEN PATINDEX('% _ %', FullName) > 0
> >> THEN SUBSTRING(FullName, CHARINDEX
> (' ', FullName) +1,
> >1)
> >> ELSE ''
> >> END AS 'MI',
> >> RIGHT(FullName, CHARINDEX(' ', REVERSE
> (FullName)) - 1) AS 'Last
> >Name'
> >> FROM Presidents
> >>
> >>
> >>
> >> You can look up the various pieces used.
> >> CHARINDEX
> >> PATINDEX
> >> SUBSTRING
> >> REVERSE
> >> CASE
> >>
> >> Rick Sawtell
> >> MCT, MCSD, MCDBA
> >>
> >>
> >
> >
> >.
> >

Query Foreign Key Columns and Tables

I am trying to query the database to get me the foreign key columns and the tables they belong to.

I have:

The name of the table

I need:

The name of the column in thetarget table

The name of the column in thereferenced table

The name of thereferenced table

Any help would be great, thanks

You can use the Management Studio diagramming tool to create it easily but through code it can get complex, the link below will take you in the right directions. Hope this helps.

http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp

|||You'd better use system procedure to do this,?which?is?always recommended. For example:

use northwind
go
EXEC sp_helpconstraint Orders

If the result set doesn't fit your need, you can perform query directly on the sysforeighkeys table, but this is not recommended for tons of reasons. For exampe:

DECLARE @.tblName sysname
SET @.tblName='Orders'

SELECT OBJECT_NAME(fkeyid) AS TargetTable,OBJECT_NAME(rkeyid) AS ReferencedTable,
OBJECT_NAME(constid) AS FKName,COL_NAME(fkeyid,fkey) AS TargetColumn,
COL_NAME(rkeyid,rkey) AS ReferencedColumn
FROM sysforeignkeys
WHERE fkeyid=OBJECT_ID(@.tblName)|||

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

|||

BurnChrome:

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

I am glad to see your probelm is resolved.

|||

BurnChrome:

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

I am glad to see your probelm is resolved.

Query for most recent of duplicate records

I need some ideas on this query.
I have a table with entries similar to the following with columns name,
id, and timestamp.
kmyoung 345 2005-08-22 07:29:00.000
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-22 09:29:11.000
rbeheler 360 2005-08-24 09:28:19.000
rbeheler 360 2005-08-29 09:27:54.000
I need a resultant set that gives me the records with the most recent
timestamp for each ID as listed below.
kmyoung 345 2005-08-29 07:29:15.000
rbeheler 360 2005-08-29 09:27:54.000
Thanks for the help.Try,
select
*
from
t1 as a
where
c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
go
AMB
"Jeff" wrote:

> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||select name, id, max(timestamp) as timestamp
from thetable
group by name, id
having count(*)>1 -- if you need just the ones that have dupes, add this
line
Jeff wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||It worked great as long as t1 was an actual table. But in actuality t1 is a
union of two tables. When I substitute (select * from t1 union select * fro
m
t2) as t1, it no longer works. Would it be possible to rewrite this with a
subquery instead of t1?
"Alejandro Mesa" wrote:
> Try,
> select
> *
> from
> t1 as a
> where
> c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> go
>
> AMB
> "Jeff" wrote:
>|||Very close, but I ended up with this resultant set instead.
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-29 09:27:54.000
I ended up with two entries for id 360.
"Trey Walpole" wrote:

> select name, id, max(timestamp) as timestamp
> from thetable
> group by name, id
> having count(*)>1 -- if you need just the ones that have dupes, add this
> line
>
> Jeff wrote:
>|||Nevermind. It worked fine by simply substituting the subquery in place of
t1. Works exactly as I need it to .
Thanks!
"Jeff" wrote:
> It worked great as long as t1 was an actual table. But in actuality t1 is
a
> union of two tables. When I substitute (select * from t1 union select * f
rom
> t2) as t1, it no longer works. Would it be possible to rewrite this with
a
> subquery instead of t1?
> "Alejandro Mesa" wrote:
>|||oops - seeing a little cross-eyed today...
Jeff wrote:
> Very close, but I ended up with this resultant set instead.
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-29 09:27:54.000
> I ended up with two entries for id 360.
> "Trey Walpole" wrote:
>

Query for 4 weeks average ..Need help

i have 3 tables, each with a date(it has daily dates) column(column name is same in all tables)
Each table has columns say "value1","value2", "value3"

i want data from all these tables together.such that my first column will have data weeks and other 3 columns count1,count2,count3 will have average of next 4 weeks count..placed infront of week.

weeks count(value1) count(value2) count(value3 )
1/1/2005 101 88 221
1/8/2005 100 81 151
1/15/2005 87 96 301

Average calculations Here :
week 1 2 3 4
Count1: 101 = ( 99 + 105 + 110 + 87 )/4
100 = (105 + 110 + 87 + 98 )/4

Plz lemme know if u have any suggestions..

Do you really mean "SUM" where you say "COUNT"?|||Also, are you wanting your weeks to run Sunday to Saturday?|||

Sumit:

I put this together. It parameterized to allow for variation of (1) a "from date", (2) a "to date", and (3) the "beginning day of the week" [here I am assuming Sunday]. This routine uses a "small_iterator" table to flash through and summarize the records that occur during the date range. I am assuming that what you want are 28-day averages from the date named through the 28 days that follow. My "small_iterator" table consists of the integers 1-32768 and is intended as a utility table that we generally make avaible to all application databases. Our standards for this table stress the use of the NOLOCK optimizer hint for this table to avoid lock contention. This simple table is defined as:

create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)

I hope the following is of use; I am not sure of all the requirements you have:

-- -
-- First, create a fake table with some fake data
-- -

set nocount on
create table ##xample
( xDate datetime not null,
value1 integer not null,
value2 integer not null,
value3 integer not null,

constraint pk_##xample primary key (xDate)
)

declare @.rootDate datetime
set @.rootDate = '11/19/2005' -- selecting a non-distinct date

declare @.iter integer
set @.iter = 0

while @.iter <= 250
begin

insert into ##xample
select dateadd (day, @.iter, @.rootDate),
1 + 60 * rand (),
1 + 40 * rand () + 40 * rand(),
1 + 50 * rand() + 50 * rand() + 50 * rand ()

set @.iter = @.iter + 1

end

--select * from ##xample -- To show the fake data if you want to see it

-- -
-- Establish some parameters to this report summary
--
-- In this example, we are going to assume that a week begins on Sunday
--
-- We are going to run this report from 1/1/2006 to the present; note
-- that since this uses an iterator table that the start date is set
-- to 12/31/2005 because dates are derived by using the iterator to
-- increment through the dates and the lowest iteration value is 1.
--
-- The @.baseWeekDate var is used to store the date on which the first
-- full week of the year begins minus one week (because of iterator table)
--
-- I am not sure about how the ranges are to run so maybe this helps,
-- maybe it doesn't
--
-- Notice that the "4-week" average rapidly shrinks for the data
-- at the end of the table; this is because we are taking a "4 week"
-- average with less than 28 days of data; you might want this handled
-- differently
-- -

declare @.fromDate datetime
declare @.toDate datetime
declare @.firstWeekDay integer
declare @.baseWeekDate datetime
declare @.maxIterator integer

set @.firstWeekDay = 1 -- Assume that Sunday is the beginning of the week
set @.fromDate = '12/31/5' -- The beginning of the year minus 1 day
set @.toDate = ( select max (xDate) from ##xample ) -- The highest date in the table
set @.maxIterator = 1 + datediff (day, @.fromDate, @.toDate) / 7 -- upper bound for iterator

-- -
-- Stuff is beginning to get more tricky here. I am looking for the
-- first Sunday the occurs at or after the "from date"; however, because
-- I am going to be using an iterator to bang throug the data, I must
-- back the that first Sunday date by a week.
-- -
select @.baseWeekDate = dateadd (day, -7 ,dateadd (day, iter, @.fromDate))
from small_iterator (nolock)
where iter <= 7
and datepart (dw, dateadd (day, iter, @.fromDate)) = @.firstWeekDay

-- Just used when I was debugging
/*
select @.fromDate as [@.fromDate],
@.toDate as [@.toDate],
@.firstWeekDay as [@.firstWeekDay],
@.baseWeekDate as [@.firstWeekDate],
@.maxIterator as [@.maxIterator]
*/

-- -
-- Heavy into it here:
--
-- This routine uses an iterator table to flash through all of the
-- starting week dates that occur between the from date and the to date
--
-- Compute the 4-week average for the data that begins with the listed
-- date and runs for the next 28 days
-- -
select convert (varchar (12), weekDate, 101) as [Week Date],
avgVal_1 as [Avg Val 1],
avgVal_2 as [Avg Val 2],
avgVal_3 as [Avg Val 3]
from ( select dateadd (day, 7*iter, @.baseWeekDate) as weekDate,
sum (value1) / 4 as avgVal_1,
sum (value2) / 4 as avgVal_2,
sum (value3) / 4 as avgVal_3
from small_iterator (nolock) -- don't want contention on an iterator
inner join ##xample
on xDate >= dateadd (day, 7*iter, @.baseWeekDate) -- bangs through all the sundays
and xDate < dateadd (day, 7*iter + 28, @.baseWeekDate) -- sets up a 4-week interval
where iter <= @.maxIterator
group by dateadd (day, 7*iter, @.baseWeekDate) -- Group the data by the week
) xx
order by weekDate

-- -
-- All done; let's drop the table and go home
-- -

go

drop table ##xample

|||Could you please post a sample schema, data and expected results?|||

I am so so thankful of u. i really wanted somthing of this type.

Now only problem is tat if the End Ref Date doesnt fall in the 4th week then the query will still give the average of 4 weeks, which is actually wrong.

i guess it should be like this

Last week --> no average

1 week b4 last week-->average of 2

2 weeks b4 last week --> avg of last 3 weeks

for other its as usual.

if u could reply me .it ll be really gr8..

Thanks & regards

Sumit

|||

Sumit:

In the comments I had:

--
-- Notice that the "4-week" average rapidly shrinks for the data
-- at the end of the table; this is because we are taking a "4 week"
-- average with less than 28 days of data; you might want this handled
-- differently

Is what you are seeking a solution to this problem that occurs over the last 28 days?

Dave

|||

Sorry for late reply..din see the Alert.

Actually ya u r rite..i was looking for average for last 28 days.

I had to make some reports on SQL Server2K Reporting Services.

The code which you sent, which included DDL n DML statements worked fine individually in Business Intelligence Studio but the dataset couldnt generate any particular fields. So i had to remove lot of things from the query, once i understood the login.it finally worked. Chart is coming fine.Thank u.

i have another question:

I have 3 fields say :

JOb Inactive Returned

ID1 2 3

ID2 5 1

ID3 2 6

ID4 1 5

ID5 5 4

ID6 2 6

ID7 1 5

i want data in such a way tat

Days_Count jobs_inactive Jobs_Returned

1 2 1

2 3 0

3 0 1

4 0 1

5 2 8 ( for 5 and Above days)

sql

query by grouping

Hi,
I have a table with data that every 2 rows have same data for most of
cloumns (11), only 4 columns have different data. I would like to query this
table so that such rows appear as single row in the result ( I don't need th
e
4 cloumns), so I used that 11 columns in my group by clause.
A large group by will be perforance problem?
ThanksGroup by and Distinct do the same thing (well, not really - ones for
aggrigating - but anyway), and using distinct will make your code a little
cleaner, rather than having a group by statement with 11 columns in it, you
can just use the word distinct to return all rows without duplicates.
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:8167FA52-468D-48BC-8D35-8F0E7BD69E46@.microsoft.com...
> Hi,
> I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query
this
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns), so I used that 11 columns in my group by clause.
> A large group by will be perforance problem?
> Thanks|||If you don't need the 4 columns that have different values, just leave them
out of the query, and add the word Distinct immediatey after the Select
Select Distinct ....
That will do the trick...
"Jen" wrote:

> Hi,
> I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query th
is
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns), so I used that 11 columns in my group by clause.
> A large group by will be perforance problem?
> Thanks|||Hi,
How about when i use the word SUM, do i have to put all the columns in the
group by?
Thanks,
Bruno N
"Jen" <Jen@.discussions.microsoft.com> escreveu na mensagem
news:8167FA52-468D-48BC-8D35-8F0E7BD69E46@.microsoft.com...
> Hi,
> I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query
this
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns), so I used that 11 columns in my group by clause.
> A large group by will be perforance problem?
> Thanks|||If you are aggrigating, yes, you would use group by instead of distinct.
Simon Worth
"Bruno N" <nylren@.hotmail.com> wrote in message
news:eB$VmrOJFHA.3596@.TK2MSFTNGP14.phx.gbl...
> Hi,
> How about when i use the word SUM, do i have to put all the columns in the
> group by?
> Thanks,
> Bruno N
> "Jen" <Jen@.discussions.microsoft.com> escreveu na mensagem
> news:8167FA52-468D-48BC-8D35-8F0E7BD69E46@.microsoft.com...
> this
need
> the
>|||Thanks, I do use min() to get the amount since some rows have null value, so
I need to use group, is there performance issue? Is it a good way or I need
to get all the rows back and let client to massage the data?
"Simon Worth" wrote:

> If you are aggrigating, yes, you would use group by instead of distinct.
> --
> Simon Worth
>
> "Bruno N" <nylren@.hotmail.com> wrote in message
> news:eB$VmrOJFHA.3596@.TK2MSFTNGP14.phx.gbl...
> need
>
>|||Group by limits the amount of records returned to your client, so it is a
good thing in that regard. Less traffic on the highway so to speak.
There's no need for the client application to loop through the records to
find the minimum amount for each group of records - the functionality is
built into SQL to accommodate aggregating data and should be utilized as
such.
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:F65899CA-80FD-45F8-8401-92CAFECA272E@.microsoft.com...
> Thanks, I do use min() to get the amount since some rows have null value,
so
> I need to use group, is there performance issue? Is it a good way or I
need
> to get all the rows back and let client to massage the data?
> "Simon Worth" wrote:
>
the
of
query|||Jen,
From yr orig post
<<< I have a table with data that every 2 rows have same data for most of
cloumns (11), only 4 columns have different data. I would like to query this
table so that such rows appear as single row in the result ( I don't need th
e
4 cloumns),
Is the column you need the minimum from one of the 11, or one of the 4?
If it's one of the 11, then you can't group by the 11, you'll need t ogroup
by the remaining 10... The one you're aggregating on cannot be in the Group
By.
If it's one of the 4, then Iguess you DO need (at least one) of the 4, hmmm
?
"Jen" wrote:
> Thanks, I do use min() to get the amount since some rows have null value,
so
> I need to use group, is there performance issue? Is it a good way or I nee
d
> to get all the rows back and let client to massage the data?
> "Simon Worth" wrote:
>|||Yes, the minimum column if from one of 4 columns.
"CBretana" wrote:
> Jen,
> From yr orig post
> <<< I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query th
is
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns),
> Is the column you need the minimum from one of the 11, or one of the 4?
> If it's one of the 11, then you can't group by the 11, you'll need t ogrou
p
> by the remaining 10... The one you're aggregating on cannot be in the Grou
p
> By.
> If it's one of the 4, then Iguess you DO need (at least one) of the 4, hm
mm?
> "Jen" wrote:
>

Query Assistance Needed - Please

Alright, I have this table called Tags. The three columns of interest
are Tags.Id, Tags.Name, Tags.ParentTagId

This is the query I am currently using:

Select Tags.Id, Tags.Name, Tags.ParentTagId

Quote:

Originally Posted by

>From Tags

WHERE Tags.Id IN (
22536,
22535
)

This outputs to:

Id Name ParentTagId
-- ---- -----
22535 Courses 148
22536 AEB3300-2204 22535

Obviously, Courses is the Parent Tag Name to AEB3300-2204. How can I
get this to show up so the results are something like

Id Name ParentTagId ParentTagName
-- ---- -----
-----
22535 Courses 148 SomeName
22536 AEB3300-2204 22535 Courses

Thank you all for your help! I truly appreciate it!Hi there,

I think this would work:

===========================================
select T1.id, T1.name, T1.ParentTagId, T2.Name As ParentTagName
fromTags T1,
Tags T2
whereT1.ParentTagId = t2.Id
===========================================

Thanks,

Marc

Andrew Tatum wrote:

Quote:

Originally Posted by

Alright, I have this table called Tags. The three columns of interest
are Tags.Id, Tags.Name, Tags.ParentTagId
>
This is the query I am currently using:
>
Select Tags.Id, Tags.Name, Tags.ParentTagId

Quote:

Originally Posted by

From Tags

WHERE Tags.Id IN (
22536,
22535
)
>
This outputs to:
>
Id Name ParentTagId
-- ---- -----
22535 Courses 148
22536 AEB3300-2204 22535
>
Obviously, Courses is the Parent Tag Name to AEB3300-2204. How can I
get this to show up so the results are something like
>
Id Name ParentTagId ParentTagName
-- ---- -----
-----
22535 Courses 148 SomeName
22536 AEB3300-2204 22535 Courses
>
Thank you all for your help! I truly appreciate it!

|||You should get a copy of TREES & HIERARCHIES IN SQL for other ways to
mode this in SQL.

Query Assistance Needed - Please

Alright, I have this table called Tags. The three columns of interest
are Tags.Id, Tags.Name, Tags.ParentTagId

This is the query I am currently using:

Select Tags.Id, Tags.Name, Tags.ParentTagId

Quote:

Originally Posted by

>From Tags

WHERE Tags.Id IN (
22536,
22535
)

This outputs to:

Id Name ParentTagId
-- ---- -----
22535 Courses 148
22536 AEB3300-2204 22535

Obviously, Courses is the Parent Tag Name to AEB3300-2204. How can I
get this to show up so the results are something like

Alright, I have this table called Tags. The three columns of interest
are Tags.Id, Tags.Name, Tags.ParentTagId

This is the query I am currently using:

Select Tags.Id, Tags.Name, Tags.ParentTagId

Quote:

Originally Posted by

>From Tags

WHERE Tags.Id IN (
22536,
22535
)

This outputs to:

Id Name ParentTagId ParentTagName
-- ---- -----
-----
22535 Courses 148 SomeName
22536 AEB3300-2204 22535 Courses

Thank you all for your help! I truly appreciate it!Sorry about the above. I copied it to verify spelling, etc and
apparently instead of replacing the text it posted below it. I
apologize!

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