Combining strings

Let's assume we have two tables - Customers and Orders.

I need a query that will return a string value containing a list of order titles from the Orders table for a particular customer.

How can this be done?

Thanks.

Hi vkh,

you have to use a function approach for this, as it can be seen on (sort of, I would vary this one to a temporary table rather than a cursor, but just to show you the iterative approach)

http://www.sqlteam.com/item.asp?ItemID=2368

HTH; jens Suessmeyer.

|||Thank you!

Combining 3 columns into one (not concatenation)

Greetings,
I am trying to "Fix" a poorly normalized table, and I wanted some info on the best way to go about this. It is an orders table that has items associated with it, and also "add-ons" to those items in the same table, like so:

order# Part# Addon1 Addon2 Addon3

What I would like to do is break the addons into a new table. Is there a way using a query/view/SP to bring all the addon fields into one column to create a new table with? or would I have to create some form of append to add the additional columns one at a time. Here is an example of what I want:

Old Table: addon1 Addon2 Addon3

New Table:
Addon1
Addon2
Addon3

Of course I would also provide a link between the part and the applicable addons.

Thanksselect Order, Part, Addon1 as Addon from [YourTable] where Addon1 is not null
UNION
select Order, Part, Addon2 as Addon from [YourTable] where Addon2 is not null
UNION
select Order, Part, Addon3 as Addon from [YourTable] where Addon3 is not null

Combined Primary Key - Why?

In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen>> If (b), then how do I ensure that a combination of ProductID and OrderID
In t-SQL, you'd use a NOT NULL UNIQUE CONSTRAINT on those values.
Anith|||I would normally go with ProductID, OrderID for primary key, as that is the
natural key. If I have to go with a surrogate key, then I'd make sure
there's a unique index/constraint on ProductID, OrderID combination.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen|||You can create a UNIQUE constraint on the combination of ProductID and
OrderID. Although my preference is to have a Primary Key on ProductID and
OrderID, and, if I think a single column (Identity) key is useful, to have
the UNIQUE constraint on the Identity column. In this scenario there is
usually not much need to have an Identity column on the ProductOrders table
though.
Jacco Schalkwijk
SQL Server MVP
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition
> for
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID
> are
> unique?
> Thanks,
> Naveen|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||I wasn't talking about the order of columns within the compound key - but
yes, that's a good point.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23NHEglEPFHA.2520@.tk2msftngp13.phx.gbl...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||There is only one key, (order_id, product_id) by definition. This is
basic RDBMS; a key must be made up of attributes that exist in the data
model. There is no ProductOrderID(mostly identity) in the real world;
it is derived from the internal state of the hardware at insertion
time:
CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Inventory(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
order_id INTEGER NOT NULL
REFERENCES Orders(order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_id, product_id));
Newbies screw up and do it the other way. They can then get duplicates
and destroy the data integrity of the schema.|||This Product Orders table is just a OrderDetails, OrderItems or
InvoiceDetails table..
If you are going have any other tables "hanging off" this table, where you
will need to have a FK referring back to the ProductOrders (I'd call it
OrderDetails)
Then I'd go with b) strictly to avoid having to use composite FKs in other
table(s).
In that event, add another "Alternate" key (using Unique Index, or Unique
Constraint, on (OrderID, ProductID) to ensure uniqueness.
Regardless of whether you also have a single column surrogate key, make the
Composite key the Clustered Index, and if more queries will select data by
OrderID than by ProductID from this table, (THis is normally true for such
tables) use (OrderID, ProductID) as the sequence. But also put another inde
x
on ProductID by itself, for Joins back to Product Table
"Naveen" wrote:

> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition fo
r
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID a
re
> unique?
> Thanks,
> Naveensqlsql

Columns Order

Dear All
Can I Alter tables to change the columns orders, Or Add Column in a
specified order? How?
AhmedAhmed
I don't think that it make sense. It does not matter what is a column's
order though you can SELECT col1,col2 Or SELECT col,col1
If you persist, you can add the column by EM in order that you want.
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:%232KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
> Ahmed
>|||No and no. Only way is to re-create the table (which is what Enterprise Mana
ger does when you do it
graphically).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:%232KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a specif
ied order? How?
> Ahmed
>|||> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
More importantly, WHY?
http://www.aspfaq.com/2528|||The only reason I can think of for changing the order of columns is that you
want to use SELECT *. Using SELECT * is a common bad practice usually
committed by the lazy or the incompetent. I only use it in query analyzer
for debugging. SELECT * is one of the things I look for in profile traces
when I'm analyzing a system. If I find it in a trace, then I know that I
will have to spend a lot more time determining whether or not any change I
make will break existing code, and consequently, I will have to charge the
customer a lot more money at every stage of the project.
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:#2KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
> Ahmed
>|||Dear All
Thanks for replay
I know it's not a live or death feature, I need to arrange the columns
according to some rules, like if it is a primary key or doesn't allow null,
to make the table structure in client database same as the development
database, or anything else. I'm just asking how to do it.
Anyway, if we can do it through the enterprise manager I think it is not
difficult to make it through the T_SQL.
Thanks
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:Om6jiC7sFHA.524@.TK2MSFTNGP12.phx.gbl...
> The only reason I can think of for changing the order of columns is that
> you
> want to use SELECT *. Using SELECT * is a common bad practice usually
> committed by the lazy or the incompetent. I only use it in query analyzer
> for debugging. SELECT * is one of the things I look for in profile traces
> when I'm analyzing a system. If I find it in a trace, then I know that I
> will have to spend a lot more time determining whether or not any change I
> make will break existing code, and consequently, I will have to charge the
> customer a lot more money at every stage of the project.
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:#2KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
>|||Ahmed Hashish (a_hashish@.hotmail.com) writes:
> Thanks for replay
> I know it's not a live or death feature, I need to arrange the columns
> according to some rules, like if it is a primary key or doesn't allow
> null, to make the table structure in client database same as the
> development database, or anything else. I'm just asking how to do it.
> Anyway, if we can do it through the enterprise manager I think it is not
> difficult to make it through the T_SQL.
You are completely right, and Brian is wrong. There are several good reasons
why one want to have columns in a certain order, and SELECT * is not one
of them. But have PK columns first in the table, and in the correct order
is one. And wanting have logically related columns close to each other is
another.
Anyway, there is no syntax for this, not even in SQL 2005. What Enterprise
Manager does is to create a new table, and then move over data to that
table, and recreate foreign keys etc. In a script, I should add, that has
several serious flaws. While these are fairly easy to address, an ALTER
TABLE command would be easier to use.
On MSDN Product Feedback Centre you can submit bugs and suggestions for
SQL 2005, and then other people can vote on these submissions. One
suggestion that was submitted earlier this year, was precisely about this
matter. By now, it has assembled 21 votes, which I think makes it the
most voted-on suggestion for SQL 2005. So you are not the first one to
ask for this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> You are completely right, and Brian is wrong. There are several good
reasons
> why one want to have columns in a certain order, and SELECT * is not one
> of them. But have PK columns first in the table, and in the correct order
> is one. And wanting have logically related columns close to each other is
> another.
The location of columns in a table is immaterial either from a performance
standpoint, or for any other reason. A primary key constraint always
creates an index, which means that the key values are copied into the a
B-tree structure, so there is no need for the columns to be adjacent.
Variable length columns are always separated from fixed length columns at
the physical layer, and depending on the option settings when the table is
created, nullable fixed-length character columns are treated in the same way
as variable-length character columns. In addition, SQL Server reads an
extent at a time, so there is no performance penalty for having intervening
columns within a row since the entire row is in memory anyway. Therefore,
it is pointless to try to force the columns to be in a specific order,
unless you're going to use SELECT *.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CE72C7ED62Yazorman@.127.0.0.1...
> Ahmed Hashish (a_hashish@.hotmail.com) writes:
columns
> You are completely right, and Brian is wrong. There are several good
reasons
> why one want to have columns in a certain order, and SELECT * is not one
> of them. But have PK columns first in the table, and in the correct order
> is one. And wanting have logically related columns close to each other is
> another.
> Anyway, there is no syntax for this, not even in SQL 2005. What Enterprise
> Manager does is to create a new table, and then move over data to that
> table, and recreate foreign keys etc. In a script, I should add, that has
> several serious flaws. While these are fairly easy to address, an ALTER
> TABLE command would be easier to use.
> On MSDN Product Feedback Centre you can submit bugs and suggestions for
> SQL 2005, and then other people can vote on these submissions. One
> suggestion that was submitted earlier this year, was precisely about this
> matter. By now, it has assembled 21 votes, which I think makes it the
> most voted-on suggestion for SQL 2005. So you are not the first one to
> ask for this.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||> I also like to start the table definition with the columns of the
> Primary Key, preferably in the order of the Primary Key definition.
Sure, when you're first designing the table, I think we all tend to create
the column structure in a logical order.
Whether it is a logical thing to do after the table has been created, I'm
not so sure about that. Why would we be adding the primary key to the table
as an afterthought?
More often than not, this request seems to stem from issues like "I want all
my numeric columns together" or "I want such and such column at the 'end' of
the table."|||Brian Selzer (brian@.selzer-software.com) writes:
> The location of columns in a table is immaterial either from a
> performance standpoint, or for any other reason. A primary key
> constraint always creates an index, which means that the key values are
> copied into the a B-tree structure, so there is no need for the columns
> to be adjacent. Variable length columns are always separated from fixed
> length columns at the physical layer, and depending on the option
> settings when the table is created, nullable fixed-length character
> columns are treated in the same way as variable-length character
> columns. In addition, SQL Server reads an extent at a time, so there is
> no performance penalty for having intervening columns within a row since
> the entire row is in memory anyway. Therefore, it is pointless to try
> to force the columns to be in a specific order, unless you're going to
> use SELECT *.
If only computers were reading the table, you would be right. Almost, more
a little later.
But the table is also used by people. If you are going to develop something
in a database, it may be perfectly OK to you if you look at the table
definition and the columns appear in the order they were added to the table.
Personally, I prefer to see column in a logical order, for instance PK
column first, auditing columns at the end, and related column adjancent
to each other.
This also matters when you do a SELECT * from Query Analyzer for debugging
reasons. Which I do a lot. (SELECT * in code is another matter.)
There is also a technical reason. Say that your column order is accidental,
and now you are to bulk load out from a table one server to the same table
on another server. If you know about it, you write a format file (jolly
good fun for a 100-column table!). If you don't know about it, you may
be informed of errors. Then again you may not, because the colunms that
were in different order were of the same data types.
Column order is not about performance. It's about usability.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp