By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

how to generate the following output in sql ?

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.