Data Display using SQL

Question

I have a data in SQL table in this order:

Order Num Material ReqstDate   ReqstQty  ConfDate    ConfQty ShippedDate ShippedQty
===== === ======== ==========  ========  ==========  ======= =========== ==========
1001  1   ABC      01/10/2011  2500      01/12/2011  500     01/13/2011  500 
1001  1   ABC      99/99/9999  0         01/15/2011  2000    01/17/2011  1000
1001  1   ABC      99/99/9999  0         99/99/9999  0       01/19/2011  700
1001  1   ABC      99/99/9999  0         99/99/9999  0       01/21/2011  300
1001  2   EFG      02/15/2011  3750      02/20/2011  3500    02/21/2011  3000
1001  2   EFG      99/99/9999  0         99/99/9999  0       02/22/2011  500

I want the above data to be displayed in this manner:

Order Num Material ReqDate    ReqQty ConfDate    ConfQty ShippedDate ShippedQty
===== === ======== =======    ====== ==========  ======= =========== ==========
1001  1   ABC      01/10/2011 2500   01/12/2011  500     01/13/2011  500 
                                     01/15/2011  2000    01/17/2011  1000
                                                         01/19/2011  500
                                                         01/20/2011  500
      2   EFG      02/15/2011 3750   02/10/2011  3500    01/21/2011  3000
                                                         01/22/2001  500

Your question isn't very clear - are you looking for an sql query that will query the table and return some values as blank or null? If so that isn't really possible. — Ralph
– Ralph, Commented Feb 11, 2011 at 7:39
mysql or tsql? tsql is sql server. You specified conflicting tags — RichardTheKiwi
– RichardTheKiwi, Commented Feb 11, 2011 at 7:52
Are those 99/99/9999 really stored as 99/99/9999? Is it a varchar column? — RichardTheKiwi
– RichardTheKiwi, Commented Feb 11, 2011 at 7:52

bluish · Accepted Answer · 2011-02-11 08:32:01Z

2

It is not possible with a SQL query.

It seems an issue that can be solved with a reporting tool (for example BIRT for Eclipse). With it you can group the fields as you want.

edited Feb 11, 2011 at 8:32

answered Feb 11, 2011 at 7:52

bluish

27.6k28 gold badges126 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikael Eriksson · Accepted Answer · 2011-02-11 15:32:47Z

SQL Server You want this to be displayed. That is usually done somewhere not in SQL. But if you set output to text in MSSMS you can use this to get the desired output.

declare @T table
(
  [Order] char(4), Num char(1), Material char (3), ReqstDate char(10), ReqstQty char(4),
  ConfDate char(10), ConfQty char(4), ShippedDate char(10), ShippedQty char(4)
)

insert into @T values
('1001', '1', 'ABC', '01/10/2011', '2500', '01/12/2011', '500 ', '01/13/2011', '500 '), 
('1001', '1', 'ABC', '99/99/9999', '0   ', '01/15/2011', '2000', '01/17/2011', '1000'),
('1001', '1', 'ABC', '99/99/9999', '0   ', '99/99/9999', '0   ', '01/19/2011', '700 '),
('1001', '1', 'ABC', '99/99/9999', '0   ', '99/99/9999', '0   ', '01/21/2011', '300 '),
('1001', '2', 'EFG', '02/15/2011', '3750', '02/20/2011', '3500', '02/21/2011', '3000'),
('1001', '2', 'EFG', '99/99/9999', '0   ', '99/99/9999', '0   ', '02/22/2011', '500 ')

;with cte as
(
  select
    [Order],
    Num,
    Material,
    ReqstDate,
    ReqstQty,
    ConfDate,
    ConfQty,
    ShippedDate,
    ShippedQty,
    row_number() over(order by cast(ShippedDate as datetime)) as n
  from @T
)
select
  isnull(nullif(C1.[Order], C2.[Order]), '') as [Order],
  isnull(nullif(C1.Num, C2.Num), '') as Num,
  isnull(nullif(C1.Material, C2.Material), '') as Material,
  isnull(nullif(C1.ReqstDate, '99/99/9999'), '') as ReqstDate,
  isnull(nullif(C1.ReqstQty, '0   '), '') as ReqstQty,
  isnull(nullif(C1.ConfDate, '99/99/9999'), '') as ConfDate,
  isnull(nullif(C1.ConfQty, '0   '), '') as ConfQty,
  isnull(nullif(C1.ShippedDate, '99/99/9999'), '') as ShippedDate,
  isnull(nullif(C1.ShippedQty, '0   '), '') as ShippedQty
from cte as C1
  left outer join cte as C2
    on C1.n = C2.n+1
order by C1.n

Andriy M · Accepted Answer · 2011-02-11 10:03:56Z

SQL Server 2005+.

;
WITH
  data ([Order], Num, Material, ReqstDate, ReqstQty, ConfDate, ConfQty,
    ShippedDate, ShippedQty)
  AS (
    SELECT 1001, 1, 'ABC', '01/10/2011', 2500, '01/12/2011',  500, '01/13/2011',  500  UNION ALL
    SELECT 1001, 1, 'ABC', '99/99/9999',    0, '01/15/2011', 2000, '01/17/2011', 1000  UNION ALL
    SELECT 1001, 1, 'ABC', '99/99/9999',    0, '99/99/9999',    0, '01/19/2011',  700  UNION ALL
    SELECT 1001, 1, 'ABC', '99/99/9999',    0, '99/99/9999',    0, '01/21/2011',  300  UNION ALL
    SELECT 1001, 2, 'EFG', '02/15/2011', 3750, '02/20/2011', 3500, '02/21/2011', 3000  UNION ALL
    SELECT 1001, 2, 'EFG', '99/99/9999',    0, '99/99/9999',    0, '02/22/2011',  500
  ),
  numbereddata AS (
    SELECT
      [Order], Num, Material, ReqstDate, ReqstQty,
      ConfDate, ConfQty, ShippedDate, ShippedQty,
      rownum1 = ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY Num, CONVERT(datetime, ShippedDate)),
      rownum2 = ROW_NUMBER() OVER (PARTITION BY [Order], Num ORDER BY CONVERT(datetime, ShippedDate))
    FROM data
  )
SELECT
  [Order]  = CASE rownum1 WHEN 1 THEN CAST([Order] AS varchar) ELSE '' END,
  Num      = CASE rownum2 WHEN 1 THEN CAST(Num AS varchar) ELSE '' END,
  Material = CASE rownum2 WHEN 1 THEN CAST(Num AS varchar) ELSE '' END,

  ReqstDate = CASE ReqstDate WHEN '99/99/9999' THEN '' ELSE ReqstDate END,
  ReqstQty  = CASE ReqstDate WHEN '99/99/9999' THEN '' ELSE CAST(ReqstQty AS varchar)  END,

  ConfDate = CASE ConfDate WHEN '99/99/9999' THEN '' ELSE ConfDate END,
  ConfQty  = CASE ConfDate WHEN '99/99/9999' THEN '' ELSE CAST(ConfQty AS varchar)  END,

  ShippedDate = CASE ShippedDate WHEN '99/99/9999' THEN '' ELSE ShippedDate END,
  ShippedQty  = CASE ShippedDate WHEN '99/99/9999' THEN '' ELSE CAST(ShippedQty AS varchar)  END
FROM numbereddata nd
ORDER BY nd.[Order], nd.Num, CONVERT(datetime, nd.ShippedDate)

Community · Accepted Answer · 2017-05-23 10:33:12Z

This is possible if you are using tsql (i.e. microsoft sql server. You also tagged your question with MySql which is another brand of database) with a sql server that supports ROW_NUMBER (i.e. Sql Server 2005 or later).

I'm assuming that ReqstDate, ConfDate and ShippedDate are nvarchars (otherwise it would be impossible to have the value '99/99/9999' in those fields). You should convert those columns into datetime fields otherwise all the ORDER BY clauses in the query will go wrong when you get a value like 01/15/2012 (The query works for the dataset you gave but only because the datetime values are all near each other in time so that the alphabetical order is equal to the chronological order). Off course, if you you are going to do this, you will need to use NULL for '99/99/9999' and in the query change '!= '99/99/9999'' to 'is null'.

However, I would advise to use a reporting tool or a simple select and some code to generate the table you want. The query is rather complex and difficult to maintain. As a general rule, a database provides the caller with data while all the formatting should be done by the caller itself.

Edit : Mikael Eriksson's answer is better & simpler than mine. My remark about the datetime fields still stands however.

select case when ROW_NUMBER() over (partition by [Order] Order by [order]) = 1
             and [order] != 0
       then cast([Order] as nvarchar)
       else ''
   end as [order],
   case when ROW_NUMBER() over (partition by [order], num 
                                Order by [order], num) = 1
             and num != 0
       then cast(num as nvarchar)
       else ''
   end as num,
   case when ROW_NUMBER() over (partition by [order], num, Material 
                                Order by [order], num, Material) = 1
       then Material
       else ''
   end as Material,
   case when ROW_NUMBER() over (partition by [order], num, Material, ReqstDate 
                                Order by [order], num, Material, ReqstDate) = 1
             and ReqstDate != '99/99/9999'
       then ReqstDate
       else ''
   end as ReqstDate,       
   case when ROW_NUMBER() over (partition by [order], num, Material, ReqstDate, ReqstQty 
                                Order by [order], num, Material, ReqstDate, ReqstQty) = 1
             and ReqstQty != 0
       then cast(ReqstQty as nvarchar)
       else ''
   end as ReqstQty, 
   case when ROW_NUMBER() over (partition by [order], num, Material, ReqstDate, ReqstQty, ConfDate 
                                Order by [order], num, Material, ReqstDate, ReqstQty, ConfDate) = 1
             and ConfDate != '99/99/9999'
       then ConfDate
       else ''
   end as ConfDate,    
   case when ROW_NUMBER() over (partition by [order], num, Material, ReqstDate, ReqstQty, ConfDate, ConfQty 
                                Order by [order], num, Material, ReqstDate, ReqstQty, ConfDate, ConfQty) = 1
             and ConfQty != 0
       then cast(ConfQty as nvarchar)
       else ''
   end as ConfQty,     
   case when ROW_NUMBER() over (partition by ShippedDate, num, Material, ReqstDate, ReqstQty, ConfDate, ConfQty, ShippedDate 
                                Order by ShippedDate, num, Material, ReqstDate, ReqstQty, ConfDate, ConfQty, ShippedDate) = 1
             and ShippedDate != '99/99/9999'
       then ShippedDate
       else ''
   end as ShippedDate,     
   case when ROW_NUMBER() over (partition by [order], num, Material, ReqstDate, ReqstQty, ConfDate, ConfQty, ShippedDate, ShippedQty 
                                Order by [order], num, Material, ReqstDate, ReqstQty, ConfDate, ConfQty, ShippedDate, ShippedQty) = 1
             and ShippedQty != 0
       then cast(ShippedQty as nvarchar)
       else ''
   end as ShippedQty
   from yourTable
   order by ROW_NUMBER() over (order by [order], num, Material, ReqstDate, ReqstQty, ConfDate, ConfQty, ShippedDate, ShippedQty)

Collectives™ on Stack Overflow

Data Display using SQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related