Multiple rows to one row

[Guest]

I have table with two fields. One field is number and other one refrence codes.
Number of rows for a given number varies. I want to convert table to a query
with one row per number and all refernce code info into multiple columns.
E.g Input Data (Number of occurences of "1" can vary)
1 - a
1 - b
1 - c
2 - d
2 - e
Result expected
1 - a, b, c
2 - d,e

 

[Jamie Collins]

I have table with two fields. One field is number and other one refrence codes.
Number of rows for a given number varies. I want to convert table to a query
with one row per number and all refernce code info into multiple columns.
E.g Input Data (Number of occurences of "1" can vary)
1 - a
1 - b
1 - c
2 - d
2 - e
Result expected
1 - a, b, c
2 - d,e

Sure, add a GROUP BY clause and use the CONCATENATE set function in
the SELECT clause. You'll find details of Access/Jet's set functions
(colloquially 'aggregate functions') here:

SQL Aggregate Functions
http://office.microsoft.com/en-gb/access/HA012315011033.aspx

Avg
Count
First, Last
Min, Max
StDev, StDevP
Sum
Var, VarP

Hang on a minute: there is no Concatenate function listed! Perhaps,
then, its designers chose not to violate First Normal Form after all.

I jest, of course:

Guide to multivalued fields
http://office.microsoft.com/en-gb/access/HA012337221033.aspx

Jamie.

--

 

[Marshall Barton]

Sure, add a GROUP BY clause and use the CONCATENATE set function in
the SELECT clause. You'll find details of Access/Jet's set functions
(colloquially 'aggregate functions') here:

Duane Hookom's Concatenate function is available at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[Jamie Collins]

Duane Hookom's Concatenate function is available at:http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Gene...

It violates 1NF, right? Yesterday you said, "denormalization for
performance issues is always a question, for which the answer is an
emphatic NO 99.99% of the time." Today you seems to be saying YES to
denormalization.

Jamie.

--

 

[John Spencer]

I don't think that PRESENTING the data in a format where you have
concatenated multiple values violates 1NF. Storing the data is a different
story.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

It violates 1NF, right? Yesterday you said, "denormalization for
performance issues is always a question, for which the answer is an
emphatic NO 99.99% of the time." Today you seems to be saying YES to
denormalization.

Now you're starting to sound like a troll looking for an
argument. No one is talking about stuffing a concatenated
list into a table so your accusation is totally specious.

 

[John W. Vinson]

Now you're starting to sound like a troll looking for an
argument.

Wouldn't be the first time by a long shot.

John W. Vinson [MVP]

 

[Guest]

Jamie,
Thanks for responce. I am novice at access and do not have clue of SQL
statements. basically even if it is not possible to make it "a,b,c" but I can
convert it to three coulmns it is OK. Is it possible to provide some sample
code?
Abhay

 

[Jamie Collins]

I don't think that PRESENTING the data in a format where you have
concatenated multiple values violates 1NF.

I entirely agree. The OP said nothing about it being intended for the
presentation layer but did mention a query...

Storing the data is a different
story.

Indeed. I'm not familiar with the code in question so tell me: is
there something inherent in the code to prevent it being used in a
stored Query object (non-parameterized, one that returns a rsultset)?
Such an object is called a VIEW and is a virtual table e.g. you can
JOIN it to another table in a query. A virtual table is a table and
AFAIK this Concatenate function would violate 1NF if used in a stored
query.

Jamie.

--

 

[Jamie Collins]

Wouldn't be the first time by a long shot.

John W. Vinson [MVP]

Gentlemen,
I realise a *resultset* doesn't have to be normalised but AFAIK this
Concatenate function is intended to be used in a *query* (the OP's
word). Again, I urge you to consider that both ANSI standard and
Access/Jet SQL syntax does not include a Concatenate set function.

If this 'non-standard' Concatenate function is used in a persisted/
stored query, and I can't see anything that would prevent this, it
becomes a virtual table with a non-scalar column. That's a 1NF
violation by any authority on the subject, of which I am not one hence
I have no intention of arguing with anyone with a differing view of
what 1NF means to them. If anyone is interested in doing so, perhaps
they could tell which normal form they consider the following virtual
table would be in (or should I say non-table because IMO it is in
NFNF) and how to JOIN it to the Products table. ANSI-92 Query Mode
aircode SQL DLL syntax using Northwind:

CREATE VIEW MyNonTable (
order_ID, concatenated_product_IDs
)
AS
SELECT OrderID, Concatenate(ProductID)
FROM [Order Details]
GROUP BY OrderID;

Jamie.

--