how to generate the following output in sql ?

[skywalker]

I am really really new to sql,please forgive my
ignorence.

here is my problem

I have this table named mis_outtrack

desc mis_outtrack;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pub_code | varchar(3) | YES | | NULL | |
| status_flag | varchar(2) | YES | | NULL | |
| user_id | varchar(15) | YES | | NULL | |
| entry_date | date | YES | | NULL | |
| pub_date | date | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+

select * from mis_outtrack;
+----------+-------------+---------+------------+------------+
| pub_code | status_flag | user_id | entry_date | pub_date |
+----------+-------------+---------+------------+------------+
| SND | RE | AMY | 2008-11-01 | 2008-11-01 |
| SND | AU | AMY | 2008-11-05 | 2008-11-01 |
| SND | SE | AMY | 2008-11-10 | 2008-11-01 |
| MND | RE | AMY | 2008-11-03 | 2008-11-02 |
| MND | AU | AMY | 2008-11-15 | 2008-11-02 |
| MND | SE | AMY | 2008-11-15 | 2008-11-02 |
+----------+-------------+---------+------------+------------+

here pub_code is short name for a particular news paper
useid is the user who is updating the records
pub_date is the date of publication of that news paper
and
entry_date is the date when the user is updating the inserting
the status of that publication

here RE means recieved
AU means audited
SE means send
there can only be these three status flags
I have to write an sql to display this data in the following format
------------------------------------------------------------------------------------------------------------
pub_code |entry_date |entry_date |entry_date |
pub_date |
(for a |(when | (when |
(when |(publication |
particular |status was RE)|status was AU)|status was SE |date
for |
pub_code) | |
| |that news paper|
--------------------------------------------------------------------------------------------------------------
| SND 2008-11-01 2008-11-05 2008-11-10
2008-11-01
| MND 2008-11-03 2008-11-15 2008-11-15
2008-11-02
|
|
how can I do this in sql
any help will be appriciated.

Thanks in advance

[Plamen Ratchev]

Here is how you can pivot the dates. Note the use of MAX for the
pub_date, seems it is the same for each status, so MAX or MIN will work.

SELECT pub_code,
MAX(CASE WHEN status_flag = 'RE' THEN entry_date END) AS
entry_date_re,
MAX(CASE WHEN status_flag = 'AU' THEN entry_date END) AS
entry_date_au,
MAX(CASE WHEN status_flag = 'SE' THEN entry_date END) AS
entry_date_se,
MAX(pub_date) AS pub_date
FROM mis_outtrack
GROUP BY pub_code;

--
Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

skywalker (ma***********@gmail.com) writes:

I am really really new to sql,please forgive my
ignorence.

here is my problem

I have this table named mis_outtrack

desc mis_outtrack;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pub_code | varchar(3) | YES | | NULL | |
| status_flag | varchar(2) | YES | | NULL | |
| user_id | varchar(15) | YES | | NULL | |
| entry_date | date | YES | | NULL | |
| pub_date | date | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+

Hm, are you using SQL Server at all? While the above would be a legal
table definition in SQL 2008, there is no DESC command in SQL Server,
and SQL Server you would not produce that output.

The query Plamen posted is 100% ANSI SQL as far as I know, so it
should work no matter the product. If not, you need to find a forum
for the engine you are using.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Tony Mountifield]

In article <Xn**********************@127.0.0.1>,
Erland Sommarskog <es****@sommarskog.sewrote:

skywalker (ma***********@gmail.com) writes:

I am really really new to sql,please forgive my
ignorence.

here is my problem

I have this table named mis_outtrack

desc mis_outtrack;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pub_code | varchar(3) | YES | | NULL | |
| status_flag | varchar(2) | YES | | NULL | |
| user_id | varchar(15) | YES | | NULL | |
| entry_date | date | YES | | NULL | |
| pub_date | date | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+

Hm, are you using SQL Server at all? While the above would be a legal
table definition in SQL 2008, there is no DESC command in SQL Server,
and SQL Server you would not produce that output.

Looks like MySQL to me.

Cheers
Tony
--
Tony Mountifield
Work: to**@softins.co.uk - http://www.softins.co.uk
Play: to**@mountifield.org - http://tony.mountifield.org