Combining Two Tables Via T-SQL

Hello,

I have two tables that have different column names so I can not combine them using UNION statement. Is there a way to combine two tables and have all the columns from both tables.

Thank you for your help!

UNION does not require that the column names be the same, only that the datatypes are similar enough to combine. See this Example:

Code Snippet

USE Northwind
GO

SELECT CompanyName FROM Customers
UNION
SELECT FirstName + ' ' + LastName FROM Employees

CompanyName
-
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Andrew Fuller
Anne Dodsworth
Antonio Moreno Taquería
Around the Horn
Berglunds snabbk?p
Bill Smith
...

|||

Donnie:

The column names do not have to be the same for you to union together two tables. If you are trying to union together to tables column-for-column, it is sufficient to have:

The number of columns the same The datatypes of corresponding columns be the same|||

Can you expand on what you are trying to accomplish?. As long as the data type of the columns be the same, including collation, then there is no problem using union or "union all".

declare @.t1 table(c1 int, c2 int)

declare @.t2 table(c3 int, c4 int)

insertinto @.t1 values(1, 2)

insertinto @.t2 values(3, 4)

select c1, c2 from @.t1

union all

select c3, c4 from @.t2

AMB

|||

Are you certain that it is a UNION that you need to perform, and not a JOIN?

A JOIN will allow you to return all columns from both tables as individual columns within the same resultset (i.e. merge the data vertically), like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1a Column2a Column3a Column1b Column2b Column3b

--

1 T1C2R1 T1C3R1 1 T2C2R1 T2C3R1

2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2

3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3

SELECT t1.Column1a,
t1.Column2a,
t1.Column3a,
t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1a = t2.Column1b

A UNION will allow you to horizontally merge the data from both tables, like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1 Column2 Column3

-

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

SELECT t1.Column1a AS Column1,
t1.Column2a AS Column2,
t1.Column3a AS Column3

FROM Table1 t1

UNION ALL

SELECT t2.Column1b,
t2.Column2b,
t2.Column3b

FROM Table2 t2

Chris|||

Some kind of join is probably a good idea since I want to join matching rows as well as non matching rows from both tables. Maybe, a full join would be good but I don't want duplicates. Please see my example of the output. What do you think?

Thanks for your help!

Table 1 - Sample Data Column1a Column2a Column3a 1 T1C2R1 T1C3R1 2 T1C2R2 T1C3R2 3 T1C2R3 T1C3R3 5 T1C2R5 T1C3R5 Table 2 - Sample Data Column1b Column2b Column3b 4 T2C2R4 T2C3R4 2 T2C2R2 T2C3R2 3 T2C2R3 T2C3R3 6 T2C2R6 T2C3R6 Ouptput: Column1a Column2a Column3a Column1b Column2b Column3b 1 T1C2R1 T1C3R1 NULL NULL NULL 2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2 3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3 NULL NULL NULL 4 T2C2R4 T2C3R4 5 T1C2R5 T1C3R5 NULL NULL NULL NULL NULL NULL 6 T2C2R6 T2C3R6

|||

Yes, a full join should work for you.

SELECT a.Column1a, a.Column2a, a.Column3a,

b.Column1b, b.Column2b, b.Column3b

FROM Table1 a FULL JOIN Table2 b ON (a.Column1a = b.Column1b)

There should not be any duplicates in the result set.

Combining two select statements

I have a SP returning the following result
The select statement for this is

Code:

SELECT dbo.TEST1.[OFFICE NAME],COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCountFROM dbo.Test2INNERJOIN dbo.test3INNERJOIN dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryIDWHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)GROUP BY dbo.TEST1.[OFFICE NAME]ORDER BYCOUNT(dbo.TEST1.[ACCOUNT ID])DESC

name id count

case1 226 320
case2 219 288
case3 203 163
case4 223 90
case5 224 73

i have another select stnat which returns like this
The select statement is

Code:Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name]
order by count(test1.[office name]) DESC

name count
case6 10
case2 56
case4 66
case1 74
case3 88
case7 100
case5 177

How can i combine this select stament with the SP, so that, i get a fourth column with

case1 226 320 74
case2 219 288 56
....................
.....................

Hope i am not confusing you all
Please help me, if someone knows how to combine this?

Thanks

Use an alias for the Office Name column for both statements and add the id column to your first select( you need to add this column to your the GROUP BY list). Then you can use an INNER JOIN on this name column and retrieve all three columns.

Something like:

SELECT t1.name, t1.id, t1.AccountCount, t2.AccountCount2 FROM (SELECT dbo.TEST1.[OFFICE NAME] as name, [ACCOUNT ID] as id,COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCount
FROM dbo.Test2INNERJOIN
dbo.test3INNERJOIN
dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN
dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN
dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)
GROUP BY name, id ) t1 INNER JOIN (Select test1.[office name] as name, count(test1.[office name]) as AccountCount2 From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name] ) t2 ON t1.name=t2.name
ORDER BY t1.AccountCount DESC

|||

I think you've forgotten a column in your first select statement. Your first select statement selects only two columns while the output shows three columns, name, id and count. Please check and repost.

Combining results from 2 databases in 1 table

Hi, I hope someone will take a moment to answer my question.
I'm combining results from 2 databases. The SQL statement works fine in
Query Analyser using the full names of the database tables (db1.dbo.table1,
db2.dbo.table1). In SRS the dataset executes correctly under the data tab
when using the master table as the shared datasource.
However in the layout tab I dont have access to any of the fields in my
dataset.
Can anyone help me display the contents of the dataset in SRS.
Many thanks for taking the time to answer this.
SteveB.Have you tried clicking the refresh button on the data tab?
"fisab" wrote:
> Hi, I hope someone will take a moment to answer my question.
> I'm combining results from 2 databases. The SQL statement works fine in
> Query Analyser using the full names of the database tables (db1.dbo.table1,
> db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> when using the master table as the shared datasource.
> However in the layout tab I dont have access to any of the fields in my
> dataset.
> Can anyone help me display the contents of the dataset in SRS.
> Many thanks for taking the time to answer this.
> SteveB.|||Silly oversight of mine - thanks for the help.
Is this the best method of getting data from 2 databases (on the same
server)? I assume I'd only use a linked server if the databases were on
different machines.
"David Siebert" wrote:
> Have you tried clicking the refresh button on the data tab?
> "fisab" wrote:
> > Hi, I hope someone will take a moment to answer my question.
> >
> > I'm combining results from 2 databases. The SQL statement works fine in
> > Query Analyser using the full names of the database tables (db1.dbo.table1,
> > db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> > when using the master table as the shared datasource.
> > However in the layout tab I dont have access to any of the fields in my
> > dataset.
> >
> > Can anyone help me display the contents of the dataset in SRS.
> > Many thanks for taking the time to answer this.
> > SteveB.|||As far as I know, that's the best way to get data from both databases. You
might post to the sql discussions group and probably get a more informed
response there.
"fisab" wrote:
> Silly oversight of mine - thanks for the help.
> Is this the best method of getting data from 2 databases (on the same
> server)? I assume I'd only use a linked server if the databases were on
> different machines.
> "David Siebert" wrote:
> > Have you tried clicking the refresh button on the data tab?
> >
> > "fisab" wrote:
> >
> > > Hi, I hope someone will take a moment to answer my question.
> > >
> > > I'm combining results from 2 databases. The SQL statement works fine in
> > > Query Analyser using the full names of the database tables (db1.dbo.table1,
> > > db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> > > when using the master table as the shared datasource.
> > > However in the layout tab I dont have access to any of the fields in my
> > > dataset.
> > >
> > > Can anyone help me display the contents of the dataset in SRS.
> > > Many thanks for taking the time to answer this.
> > > SteveB.

combining multiple rows in 1 row

how can i get a 1 row result set having multiple rows joined into 1 row
if i have 1 column having 5 rows i want to use a select statement that selects all rows joined into 1 row (results seperated by a comma for example)
thx
samhamWant to show us the query? What would make them join together?|||see Using COALESCE to Build Comma-Delimited String (http://sqlteam.com/item.asp?ItemID=2368)

rudy
http://r937.com/|||He wants to combine multiple rows..

And you don't use COALESCE to build a comma delimited srting..

Is used so that any null value in the string does not blow away the results...

It's the ability to do SELECT @.x = @.X + col1

like...

DECLARE @.x varchar(8000)
SELECT @.x = ISNULL(@.x,'') + ISNULL(FirstName,'') FROM Employees
SELECT @.x

The coalesec trick allows you to eliminmate commas if the value in the column is Null

so you dont get 1,,2,3,4,,5|||He wants to combine multiple rows yes, he wants the values from multiple rows to be put into a comma-delimited string
And you don't use COALESCE to build a comma delimited srting damned straight on that one, i certainly don't, i would never do it that way -- in fact, i would probably just never do it
The coalesec trick allows you to eliminmate commas if the value in the column is Null yes, that's correct, that's what it does for the first row

rudy|||thx guys that's exactly what i wanted

i'll use the code from the article

DECLARE @.EmployeeList varchar(100)

SELECT @.EmployeeList = COALESCE(@.EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @.EmployeeList
--Results--

---
1, 2, 4

Combining DELETE and JOIN statements

In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:

DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL

SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?

GerritYou could try with a NOT IN.

DELETE FROM GroupsMembers WHERE GroupID NOT IN (SELECT ID FROM Groups)
|||Thanks, that does seem to do the trick.

Gerrit

Combining date field and time field in a column

SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please reply to me at Babies001@.yahoo.co
ThankWhat datatypes are you using for storing your date and time values? If using
strings, then concatenate them and use CAST or CONVERT functions to convert
the concatenated value into a datetime value.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TM" <anonymous@.discussions.microsoft.com> wrote in message
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com...
SELECT RequireDate + ' ' + RequireTime AS date
FROM IN_Header
My Database:
Date Time
28/03/2004 01:34:09PM
After run SQL statement, my result become:
26/03/2004 01:34:09PM
Why my date minus two day? so what should i need to do? Urgent please reply
to me at Babies001@.yahoo.com
Thanks|||Regarding the Question, my database fiel
Field DataTyp
Date DateTim
Time DateTim
So my result become
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
What the code for convert the date and time together and my date will not minus two day
Can adding the source code inside
Thank
-- Narayana Vyas Kondreddi wrote: --
What datatypes are you using for storing your date and time values? If usin
strings, then concatenate them and use CAST or CONVERT functions to conver
the concatenated value into a datetime value
--
HTH
Vyas, MVP (SQL Server
http://vyaskn.tripod.com
Is .NET important for a database professional
http://vyaskn.tripod.com/poll.ht
"TM" <anonymous@.discussions.microsoft.com> wrote in messag
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com..
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please repl
to me at Babies001@.yahoo.co
Thanksqlsql

Combining 3 SQL statements

Hey all. Ive got a big problem with an sql statement Im working on.

There are 2 tables with a master/detail relationship. The Header Table
is the master, the Line Table is the detail. So for each Header, there
are many Lines, but a Line can only reference one Header.
There is a Line Total and Line Cost in each Line Record. Each Line
Record has a type.
What I want to be able to do is, for each Header, I want to Sum each
corresponding Line's Total and Cost where the type is either one value
or another. If the type is, for example, 10, only sum the Total, if its
type 2, only sum the Cost.

Therefore, after the query is executed, you should have a result set
something like this

Job : Job1 (header id)
Desc : Job0001 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job1)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job1)
--------------------------------
Job : Job2 (header id)
Desc : Job0002 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job2)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job2)
--------------------------------

etc.

Hope this makes sense. ThanksTry this one here:

Select
header_id,
header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers
INNER JOIN
line
ON line.header_id = header.header_id

HTH, jens Suessmeyer.|||Jens (Jens@.sqlserver2005.de) writes:

> Try this one here:
> Select
> header_id,
> header_desc,
> SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
> SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
> FROM headers
> INNER JOIN
> line
> ON line.header_id = header.header_id

Better:

SELECT h.header_id, h.header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers h
JOIN line ON l.header_id = h.header_id
GROUP BY h.header_id, h.header_desc

Particularly that GROUP BY clause is quite important...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You are right, thats not even better that was missing in my example.|||Is there a way to say

SUM(CASE Line_type WHEN 10 THEN costs else 0 END),
SUM(CASE Line_type WHEN NOT 10 THEN Totals else 0 END) ?|||Its OK, I figured it out.
I ended up using

SUM(CASE WHEN Line_type = 10 THEN costs else 0 END),

SUM(CASE WHEN Line_type <> 10 THEN Totals else 0 END)

THanks for the help guys :)

combining 2 or more columns "as" one column in a select statement

hi guys,
how do u combine 2 columns as one in a select statement such as:
"select firstname,lastname from tablename"
where firstname and lastname are to be combined as single column called name
.
i had difficulty finding other resources for this issue.
Thanks in advance,
AdsString concatenation. The + symbol concatenates strings in SQL. So
your example would be something like:
select rtrim(firstname) + ' ' + rtrim(lastname) as [name] from
tableowner.tablename
or
select rtrim(lastname) + ', ' + rtrim(firstname) as [name] from
tableowner.tablename
The rtrim() function just trims off trailing whitespace (I'm assuming
there wont be any leading whitespace but you can take care of that too
with ltrim()), so you don't end up with something like "John
Smith " or "Smith , John ". The rest just depends exactly
what string you want at the end of it.
*mike hodgson*
http://sqlnerd.blogspot.com
ads wrote:

>hi guys,
>how do u combine 2 columns as one in a select statement such as:
>"select firstname,lastname from tablename"
>where firstname and lastname are to be combined as single column called nam
e.
>i had difficulty finding other resources for this issue.
>Thanks in advance,
>Ads
>
>|||SELECT fname + ' ' + lname as FulName from names
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ads" <ads@.discussions.microsoft.com> wrote in message
news:53BE5122-5439-4101-A221-E3FA011C6C24@.microsoft.com...
> hi guys,
> how do u combine 2 columns as one in a select statement such as:
> "select firstname,lastname from tablename"
> where firstname and lastname are to be combined as single column called
> name.
> i had difficulty finding other resources for this issue.
> Thanks in advance,
> Ads
>|||Just to throw my $.02 in, I'd suggest doing NULL checks on any string concat
in SQL server.
So my version would look like:
SELECT ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '')
FROM TableName
Now if you want to get fancy you could throw a check in there to only output
the space if both names are present. At that point though I might use a udf
SELECT dbo.udfGetFullName(TablePK)
FROM TableName
Where the udf performs the logic required to properly format the name.
"ads" wrote:

> hi guys,
> how do u combine 2 columns as one in a select statement such as:
> "select firstname,lastname from tablename"
> where firstname and lastname are to be combined as single column called na
me.
> i had difficulty finding other resources for this issue.
> Thanks in advance,
> Ads
>

combined 2 data and separate them

I have a data grid with dropdownlist.
the dropdownlist is populated with datas wth a sql statement with 2 combined data
my sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1
When i select a value from the dropdownlist, i need to separate the data, name and id into different columns
how do i do it?
Is there a way to manipulate the sql to do such a thing?you can use the split function.

Combine two or more reports into one PDF Report

We are building a "Annual Statement" which will include a series or Reports
that will be bound into a Booklet. We will need a table of Contents (With
Page Numbers) and also need the pages to be continuous throughout.
Our question is what is the best approach to build this dynamically in
Report Server?
Build One "Master Report which includes Sub-Reports or Build the Sub Reports
and pass them into the Master?
We have a middle "Business Logic" Tier that we will be driving the creation
of these reoprts from.
Thanks!
...BryanUpdate:
I am thinking that we can programattically create a "Master RDL" that
includes the Sub Reports.
Question: would we be able to control the processing of the sub reports so
that they generate first then generate the Master Report genterating the
Table of Contents after the Sub Reports have been created (and we know the
sub Report Page Counts)
Any Suggestions would be really appreachiated
Thanks!
...Bryan
"Bryan E." wrote:
> We are building a "Annual Statement" which will include a series or Reports
> that will be bound into a Booklet. We will need a table of Contents (With
> Page Numbers) and also need the pages to be continuous throughout.
> Our question is what is the best approach to build this dynamically in
> Report Server?
> Build One "Master Report which includes Sub-Reports or Build the Sub Reports
> and pass them into the Master?
> We have a middle "Business Logic" Tier that we will be driving the creation
> of these reoprts from.
> Thanks!
> ...Bryan|||Bryan -
Did you ever get an answer? We are looking to do something similar. The
first page of one of our reports needs to be a table of contents.
Thanks
Jen
"Bryan E." wrote:
> Update:
> I am thinking that we can programattically create a "Master RDL" that
> includes the Sub Reports.
> Question: would we be able to control the processing of the sub reports so
> that they generate first then generate the Master Report genterating the
> Table of Contents after the Sub Reports have been created (and we know the
> sub Report Page Counts)
> Any Suggestions would be really appreachiated
> Thanks!
> ...Bryan
> "Bryan E." wrote:
> > We are building a "Annual Statement" which will include a series or Reports
> > that will be bound into a Booklet. We will need a table of Contents (With
> > Page Numbers) and also need the pages to be continuous throughout.
> >
> > Our question is what is the best approach to build this dynamically in
> > Report Server?
> >
> > Build One "Master Report which includes Sub-Reports or Build the Sub Reports
> > and pass them into the Master?
> >
> > We have a middle "Business Logic" Tier that we will be driving the creation
> > of these reoprts from.
> >
> > Thanks!
> > ...Bryan

combine fields and text in select statement

Is it possible to combine fields and text in a select statement?

In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:

SELECT DISTINCT GRPAS GroupName, "Year: " +YEAR + "Grade: " + GRDAS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.

If this is possible, or there's a better way to do it, I'd love some more info.

Thanks!

-Mathminded

You could do it in the SELECT statement. This kind of formatting is generally done at the application/GUI layer. You need to use single quotes for strings. Also Year is a keyword so you use square brackets.

SELECT DISTINCT GRPAS GroupName, 'Year: ' + [YEAR] + 'Grade: ' + [GRD]AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

|||

I got it to work! On a whim I decided to try single quotes and that got me farther. The error it produced then led me to this page:

http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx

Then I realized I needed to change the type for one of the columns. Thus, I ended up with this SQL statement which works:

SELECT DISTINCT GRPAS GroupName,'Year: ' +YEAR +' and Grade: ' +CAST(GRDAS CHAR(2))AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

|||

ndinakar:

You could do it in the SELECT statement. This kind of formatting is generally done at the application/GUI layer. You need to use single quotes for strings. Also Year is a keyword so you use square brackets.

SELECT DISTINCT GRPAS GroupName, 'Year: ' + [YEAR] + 'Grade: ' + [GRD]AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

Thanks, Dinakar! I thought I had figured it out quickly but you had it even faster! :-) Thanks for pointing out the keyword issue, also.

|||

The output from my working statement is:

GroupName ShowMe

AYear: 0203 and Grade: 3AYear: 0304 and Grade: 4AYear: 0405 and Grade: 5BYear: 0203 and Grade: 4BYear: 0304 and Grade: 5BYear: 0405 and Grade: 6CYear: 0203 and Grade: 5CYear: 0304 and Grade: 6CYear: 0405 and Grade: 7DYear: 0203 and Grade: 6DYear: 0304 and Grade: 7DYear: 0405 and Grade: 8EYear: 0203 and Grade: 7EYear: 0304 and Grade: 8EYear: 0405 and Grade: 9FYear: 0203 and Grade: 8FYear: 0304 and Grade: 9FYear: 0405 and Grade: 10GYear: 0203 and Grade: 9GYear: 0304 and Grade: 10GYear: 0405 and Grade: 11

With the way my dropDownList is working, the user could select any of the first 3 choices, for instance, and end up with the same group. It would really be great if I could get this to output a single GroupName and combine the other information. For instance:

GroupName ShowMe

A Years: 0203,0304,0405 and Grades: 3,4,5

B Years: 0203,0304,0405 and Grades 4,5,6

etc...

Would that be really difficult to do? I may play around with it and see if I can get it using embeded select statements. I've never tried those before. In the off chance that I'm successful, I'll post my results. :-)

|||Again, this is the task that has to be done in the application layer. You have more string functions available in .NET to manipulate the strings than in SQL Server.|||

I thought it may make things easier if I changed how the information was displayed. Rather than try to fit all that info into the dropDownList using some complicated SQL query, I'd like to create a table with columns Group, Years, and Grades so the users can refer to that when choosing just the group letter from the dropDownList. Here's a sample of the table I'd like to display to the users:

GROUP

YEARS

GRADES

A

0203, 0304, 0405

3, 4, 5

B

0203, 0304, 0405

4, 5, 6

C

0203, 0304, 0405

5, 6, 7

D

0203, 0304, 0405, 0506

6, 7, 8, 9

The database table has the data stored like this:

GRP

YEAR

GRD

A

0203

3

A

0304

4

A

0405

5

B

0203

4

B

0304

5

B

0405

6

C

0203

5

C

0304

6

C

0405

7

D

0203

6

D

0304

7

D

0405

8

D

0506

9

I've tried a bunch of different things with the GridView in Visual Studio but I'm not meeting with success. Any advice would be appreciated.

Thanks!

-Mathminded

|||

You can create a stored proc and a local table variable in it, get the values in the format you want into the table and do a select from the table at the end.

Check if this post helps in the concatenation:http://forums.asp.net/thread/1514443.aspx

Column with Select Statement

Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count?

Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this.

No, as you're seeking for a computed column, and a computed column can only be derived from other columns in the same table, so you can't add a column to the Categories table which stores the count from the Photoes table. Maybe you cancreate a view which contains the count of photoes as well as other information you need in your application, and then query on the view:

CREATE VIEW CategoryPhotoCnt AS
SELECT COUNT(PhotoID) AS PhotoCnt,c.CategoryID,c.CategoryName
FROM Categories c join Photos p ON p.CID = c.CategoryID
GROUP BY c.CategoryID,c.CategoryName

Column to Search in DB

Hi, Can anyone help me by telling the sql statement through which I should b
e
able to locate the table which contains that ColumnName...
Thanks in Advance
KDKD,
Query Analyzer includes this capability and more with the Object Search
(F4).
HTH
Jerry
"KD" <KD@.discussions.microsoft.com> wrote in message
news:B44D796D-7957-46BA-A5DE-337D6808A3A0@.microsoft.com...
> Hi, Can anyone help me by telling the sql statement through which I should
> be
> able to locate the table which contains that ColumnName...
> Thanks in Advance
> KD|||Hi,
Use the below query:-
select Table_name from information_schema.columns where
column_name='column_name'
You can also query the system table SYSCOLUMNS.
Thanks
Hari
SQL Server MVP
"KD" <KD@.discussions.microsoft.com> wrote in message
news:B44D796D-7957-46BA-A5DE-337D6808A3A0@.microsoft.com...
> Hi, Can anyone help me by telling the sql statement through which I should
> be
> able to locate the table which contains that ColumnName...
> Thanks in Advance
> KD|||Thanks Hari, That's what I really wanted !
Thanks !
KD
"Hari Pra" wrote:

> Hi,
> Use the below query:-
> select Table_name from information_schema.columns where
> column_name='column_name'
> You can also query the system table SYSCOLUMNS.
> Thanks
> Hari
> SQL Server MVP
>
> "KD" <KD@.discussions.microsoft.com> wrote in message
> news:B44D796D-7957-46BA-A5DE-337D6808A3A0@.microsoft.com...
>
>

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
BkThe order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:
> There is no 'default' ordering of rows. Itzik sheds some interesting light
> on the subject:
> http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
> http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
> http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> > Can someone tell me what defines the order of SQL output columns when i
> > use a
> > default query on a table like "SELECT * from <Table> ".
> >
> > Is there a way to alter the default ORDER of these columns?
> >
> > I am aware of the ORDER BY but will not be able to use it for various
> > reasons.
> >
> > Regards
> > Bk
>|||As mentioned by Arnie, it is considered a bad practice to use "SELECT *"
in production code (with the exception of its use in an EXISTS clause).
When using SELECT * on a table, the columns in the resultset will match
the order in the table definition. There is no way to change this using
DML. The only way to change its order (apart from explicitely naming the
columns in the desired order) is to redefine the table.
HTH,
Gert-Jan
BK-Chicago wrote:
> Can someone tell me what defines the order of SQL output columns when i use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
Bk
The order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:

> There is no 'default' ordering of rows. Itzik sheds some interesting light
> on the subject:
> http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
> http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
> http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
>

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use
a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
BkThe order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/artic...blog_92886.html
http://www.sqlmag.com/article/artic...blog_92887.html
http://www.sqlmag.com/article/artic...blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:

> There is no 'default' ordering of rows. Itzik sheds some interesting ligh
t
> on the subject:
> http://www.sqlmag.com/article/artic...blog_92886.html
> http://www.sqlmag.com/article/artic...blog_92887.html
> http://www.sqlmag.com/article/artic...blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
>|||As mentioned by Arnie, it is considered a bad practice to use "SELECT *"
in production code (with the exception of its use in an EXISTS clause).
When using SELECT * on a table, the columns in the resultset will match
the order in the table definition. There is no way to change this using
DML. The only way to change its order (apart from explicitely naming the
columns in the desired order) is to redefine the table.
HTH,
Gert-Jan
BK-Chicago wrote:
> Can someone tell me what defines the order of SQL output columns when i us
e a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk

column names, table name, user name

Hi;
I am new to SQL2000 & T-SQL
Is there a T-SQL SELECT statement I can use to query these info :
1) existing column names of a table (select * will give col name & all data
rows, I only wish to see columns names)
2) data types of columns (object viewer can do this, but is there a SELECT s
tatemtent to get these info?)
3) existing tables in a database (system & users' tables)
4) existing database users in a databaseFor the first three, you can use sp_help. For the last one, you can use
sp_helpuser.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||1) you could do - select * from table where 0=1
or to get all columns - select column_name from
INFORMATION_SCHEMA.columns
2) You can get all of this from INFORMATION_SCHEMA.columns
3) select table_name from INFORMATION_SCHEMA.tables will get you all user
tables
or select name from sysobjects where type = 's' or type = 'u' to get all
tables
4) select * from sysusers where issqluser = 1
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||Column names and other pertinent info is stored in system table syscolumns.
You can get to your first two questions joining sysobjects with syscolumns.
Question 3 is sysobjects only. Question 4 is sysusers. But after replyin
g I would like to know what
exactly you're trying to learn.
Since you can't answer this question I'm curious what you're trying to do.
What you're trying to learn is in the system tables.

Column alias in select statement - efficient?

Is a column alias in a "select" statement more efficient? (statement
parsing etc...)
Thanks, gert>> Is a column alias in a "select" statement more efficient?
Probably not, since there is hardly any evidence to support it.
Anith