Hello!
Is there any way in SQL to convert records in fields. Something like
creating a a record with 3 fields from a table with 3 records?
Thanks in advance,
Hugo MadureiraYes and no. What you've asked for is presumably one of two things:
1. Formatting for a report or display. Do that client side. SQL returns
tabular results to the client or middle tier but SQL doesn't control
how those results are formatted. Display of rows, columns, delimitting,
etc is all controlled by your client application. ADO for example
includes the GetString method to do exactly what you've asked.
2. Formatting for export to some external system. Do that in DTS, BCP
or another ETL tool. It is just a question of what delimiter you choose
for columns and rows.
Although yours is quite a common FAQ doing this in SQL has no obvious
advantage and quite a few di
vantages. We'd need more info to help
you with a specific bit of code for your case: What is the key of your
table? How do you want to define which row transposes to which column
in the result?
Meantime, here's an example:
CREATE TABLE foo (col INTEGER NOT NULL PRIMARY KEY, x INTEGER NOT NULL)
;
INSERT INTO foo (col,x) VALUES (1,777) ;
INSERT INTO foo (col,x) VALUES (2,888) ;
INSERT INTO foo (col,x) VALUES (3,999) ;
SELECT
MIN(CASE WHEN col = 1 THEN x END) AS col1,
MIN(CASE WHEN col = 2 THEN x END) AS col2,
MIN(CASE WHEN col = 3 THEN x END) AS col3
FROM foo ;
David Portas
SQL Server MVP
--|||Hi Hugo
This is usually best to do on the Front End, but if you can distinguish and
link the three records then you can use a self join to do this
e.g.
CREATE TABLE MyAttributes ( id int, type char(1), colour char(10) )
INSERT INTO MyAttributes ( id , type, colour )
SELECT 1, 'A', 'Pink'
UNION ALL SELECT 1, 'B', 'Red'
UNION ALL SELECT 1, 'C', 'Crimson'
UNION ALL SELECT 2, 'A', 'Cyan'
UNION ALL SELECT 2, 'B', 'Blue'
UNION ALL SELECT 2, 'C', 'Navy'
SELECT a.id, a.colour, b.colour, c.colour
FROM MyAttributes a
JOIN MyAttributes b ON a.id= b.id and b.type = 'B'
JOIN MyAttributes c ON a.id= c.id and c.type = 'C'
WHERE a.type = 'A'
John
"Hugo Madureira" wrote:
> Hello!
> Is there any way in SQL to convert records in fields. Something like
> creating a a record with 3 fields from a table with 3 records?
> Thanks in advance,
> Hugo Madureira
>|||Hugo
read this
http://www.aspfaq.com/show.asp?id=2462
--
Regards
R.D
--Knowledge gets doubled when shared
"Hugo Madureira" wrote:
> Hello!
> Is there any way in SQL to convert records in fields. Something like
> creating a a record with 3 fields from a table with 3 records?
> Thanks in advance,
> Hugo Madureira
>|||I'm working on a warehouse management system. My table represents the
products stored in a pallet. I don't know how many products are stored
in the pallet.
I want to copy the name of the products into a new table where the
fields are V01, V02, V02, ...
I've seen examples using case statement but I don't know beforehand the
values in the products name.
David Portas wrote:
> Yes and no. What you've asked for is presumably one of two things:
> 1. Formatting for a report or display. Do that client side. SQL returns
> tabular results to the client or middle tier but SQL doesn't control
> how those results are formatted. Display of rows, columns, delimitting,
> etc is all controlled by your client application. ADO for example
> includes the GetString method to do exactly what you've asked.
> 2. Formatting for export to some external system. Do that in DTS, BCP
> or another ETL tool. It is just a question of what delimiter you choose
> for columns and rows.
> Although yours is quite a common FAQ doing this in SQL has no obvious
> advantage and quite a few di
vantages. We'd need more info to help
> you with a specific bit of code for your case: What is the key of your
> table? How do you want to define which row transposes to which column
> in the result?
> Meantime, here's an example:
> CREATE TABLE foo (col INTEGER NOT NULL PRIMARY KEY, x INTEGER NOT NULL)
> ;
> INSERT INTO foo (col,x) VALUES (1,777) ;
> INSERT INTO foo (col,x) VALUES (2,888) ;
> INSERT INTO foo (col,x) VALUES (3,999) ;
> SELECT
> MIN(CASE WHEN col = 1 THEN x END) AS col1,
> MIN(CASE WHEN col = 2 THEN x END) AS col2,
> MIN(CASE WHEN col = 3 THEN x END) AS col3
> FROM foo ;
>|||Hugo
There is another article on web. Google for that. With this you can do that
--
Regards
R.D
--Knowledge gets doubled when shared
"Hugo Madureira" wrote:
> I'm working on a warehouse management system. My table represents the
> products stored in a pallet. I don't know how many products are stored
> in the pallet.
> I want to copy the name of the products into a new table where the
> fields are V01, V02, V02, ...
> I've seen examples using case statement but I don't know beforehand the
> values in the products name.
>
> David Portas wrote:
>|||> I want to copy the name of the products into a new table where the
> fields are V01, V02, V02, ...
Why? That sounds like a big design error. Lookup "repeating group" and
First Normal Form if you don't understand why. Product is a single
attribute and should be modelled in a single column.
David Portas
SQL Server MVP
--|||I know exactly what first normal form is. The point here is that my
table with all the products wouldn't be used to store information, just
as a list of products in the pallet to be printed.
David Portas wrote:
>
> Why? That sounds like a big design error. Lookup "repeating group" and
> First Normal Form if you don't understand why. Product is a single
> attribute and should be modelled in a single column.
>|||That was my first assumption too. So I refer you to my original answer:
Reporting isn't a database function.
Of course it's perfectly possible to implement these these things in
the database, but not usually desirable. Try the following or see the
link that R.D. posted.
SELECT
MIN(CASE WHEN col = 1 THEN productname END) AS col1,
MIN(CASE WHEN col = 2 THEN productname END) AS col2,
MIN(CASE WHEN col = 3 THEN productname END) AS col3
/* ... etc */
FROM
(SELECT P1.productname, COUNT(*) AS col
FROM northwind.dbo.products AS P1
JOIN northwind.dbo.products AS P2
ON P1.productname >= P2.productname
GROUP BY P1.productname) AS T ;
David Portas
SQL Server MVP
--
