468,242 Members | 1,613 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

how to generate the following output in sql ?

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
Nov 10 '08 #1
3 1265
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
Nov 10 '08 #2
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

Nov 10 '08 #3
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
Nov 11 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Fred | last post: by
3 posts views Thread by Mikael Petterson | last post: by
18 posts views Thread by Toby Newman | last post: by
reply views Thread by Vijay Chegu | last post: by
3 posts views Thread by Dennis M | last post: by
reply views Thread by ward | last post: by
2 posts views Thread by yanlu06 | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.