locked
How to write a SQL statement which can hide Duplicate values during query output RRS feed

  • Question

  • Hi,

    a. This is NOT related to SQLServer Reporting Services.

    I am writing a complex join query to bring following output.

    EnquiryNo, ClientName, State, City,        OrderNo, OrderDate, ItemName,  Qty.
    1 ,                xxxx ,        MH,  Mumbai,       1 ,       1/1/2000,       Item1,     2
    1 ,                xxxx ,        MH,  Mumbai,       1 ,       1/1/2000,       Item2,     5
    22 ,              xxxx ,        MH,  Mumbai,       2 ,       1/1/2001,       Item6,     15
    22 ,              xxxx ,        MH,  Mumbai,       2 ,       1/1/2001,       Item7,     20
    30 ,              YYYY ,        MP,  Bhopal,         45 ,     1/1/2005,       Item10,   2
    30,               YYYY ,        MP,  Bhopal,         45 ,     1/1/2005,       Item11,   1
    30,               YYYY ,        MH,  Pune,            46 ,     1/1/2005,       Item11,   1



    I want to modify SQL query such that it can output as below.

    EnquiryNo, ClientName, State, City,        OrderNo, OrderDate, ItemName,  Qty.
    1 ,                xxxx ,        MH,  Mumbai,       1 ,       1/1/2000,       Item1,     2
                                ,                                     ,                    ,       Item2,     5
    22 ,              xxxx ,        MH,  Mumbai,       2 ,       1/1/2001,       Item6,     15
                                                                                           ,       Item7,     20
    30 ,              YYYY ,        MP,  Bhopal,         45 ,     1/1/2005,       Item10,   2
                                                                                           ,       Item11,   1
                               ,        MH,  Pune,            46 ,     1/1/2005,       Item11,   1

    Possible ?      if NOT with Simple SQL Query then can it be done with Stored Procedure  OR  CommonTable Expression ?    How ?

    Regards
    Jiya.

     

     

     

    Tuesday, November 30, 2010 2:56 AM

Answers

  • I don't understand why the last record should show State, City etc details. Try the following query.

    create table #temp (EnquiryNo nvarchar(100), ClientName nvarchar(100), State nvarchar(100),
    City nvarchar(100), OrderNo nvarchar(100), OrderDate nvarchar(100), ItemName nvarchar(100), Qty nvarchar(100))
    
    insert into #temp
    select '1' , 'xxxx', 'MH', 'Mumbai', '1' ,'1/1/2000', 'Item1','2'
    union all select '1' , 'xxxx' , 'MH', 'Mumbai', '1' , '1/1/2000',    'Item2','5'
    union all select '22' , 'xxxx' , 'MH', 'Mumbai', '2' , '1/1/2001','Item6','15'
    union all select '22' , 'xxxx' , 'MH', 'Mumbai', '2' ,    '1/1/2001',    'Item7',   '20'
    union all select '30' , 'YYYY' , 'MP', 'Bhopal', '45' ,   '1/1/2005',    'Item10',  '2'
    union all select '30' , 'YYYY' , 'MP', 'Bhopal', '45' ,   '1/1/2005',    'Item11',  '1'
    union all select '30', 'YYYY' , 'MH', 'Pune',   '46' ,   '1/1/2005',    'Item11',  '1'
    
    SELECT * FROM #temp
    
    SELECT
    	CASE WHEN RR = 1 THEN EnquiryNo ELSE '' END EnquiryNo,
    	CASE WHEN RR = 1 THEN ClientName ELSE '' END ClientName,
    	CASE WHEN RR = 1 THEN State ELSE '' END State,
    	CASE WHEN RR = 1 THEN City ELSE '' END City,
    	CASE WHEN RR = 1 THEN OrderNo ELSE '' END OrderNo,
    	CASE WHEN RR = 1 THEN OrderDate ELSE '' END OrderDate,
    	ItemName,
    	Qty
    FROM
    	(SELECT
    	(ROW_NUMBER() OVER(PARTITION BY EnquiryNo ORDER BY EnquiryNo, ClientName)) [RR], *
    	FROM #temp) SUBQUERY1
    
    drop table #temp
    
    Tuesday, November 30, 2010 3:34 AM

All replies

  • It is possible in T-SQL, although it's better to be done in the report itself (and easier there). Anyway, for the T-SQL solution check this FAQ

    Hide repeated values


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, November 30, 2010 3:24 AM
  • I don't understand why the last record should show State, City etc details. Try the following query.

    create table #temp (EnquiryNo nvarchar(100), ClientName nvarchar(100), State nvarchar(100),
    City nvarchar(100), OrderNo nvarchar(100), OrderDate nvarchar(100), ItemName nvarchar(100), Qty nvarchar(100))
    
    insert into #temp
    select '1' , 'xxxx', 'MH', 'Mumbai', '1' ,'1/1/2000', 'Item1','2'
    union all select '1' , 'xxxx' , 'MH', 'Mumbai', '1' , '1/1/2000',    'Item2','5'
    union all select '22' , 'xxxx' , 'MH', 'Mumbai', '2' , '1/1/2001','Item6','15'
    union all select '22' , 'xxxx' , 'MH', 'Mumbai', '2' ,    '1/1/2001',    'Item7',   '20'
    union all select '30' , 'YYYY' , 'MP', 'Bhopal', '45' ,   '1/1/2005',    'Item10',  '2'
    union all select '30' , 'YYYY' , 'MP', 'Bhopal', '45' ,   '1/1/2005',    'Item11',  '1'
    union all select '30', 'YYYY' , 'MH', 'Pune',   '46' ,   '1/1/2005',    'Item11',  '1'
    
    SELECT * FROM #temp
    
    SELECT
    	CASE WHEN RR = 1 THEN EnquiryNo ELSE '' END EnquiryNo,
    	CASE WHEN RR = 1 THEN ClientName ELSE '' END ClientName,
    	CASE WHEN RR = 1 THEN State ELSE '' END State,
    	CASE WHEN RR = 1 THEN City ELSE '' END City,
    	CASE WHEN RR = 1 THEN OrderNo ELSE '' END OrderNo,
    	CASE WHEN RR = 1 THEN OrderDate ELSE '' END OrderDate,
    	ItemName,
    	Qty
    FROM
    	(SELECT
    	(ROW_NUMBER() OVER(PARTITION BY EnquiryNo ORDER BY EnquiryNo, ClientName)) [RR], *
    	FROM #temp) SUBQUERY1
    
    drop table #temp
    
    Tuesday, November 30, 2010 3:34 AM
  • One option is:

    Get  sorted results into a temp table

    then use a cursor to loop though and write logic to set values are null wherever applicable.

     

    >Bineesh


    #Bineesh ------------------------------------ Mark best replies as answers please.
    Tuesday, November 30, 2010 3:53 AM
  • Go back to basics and actually read your database textbook.

    RDBMS is a tiered architecture. The  SQL query returns a table in 1NF or better and passes it to a host program. The host program can then do lots of things with it. It can be a reporting server which holds  and consolidates the data, sorts it and sends it to other end users or servers. It can be an application program. It can be a report writer, statistics program, graphics program , etc. 

    This display formatting is a job for a simple report writer. You can probably find an Open Source tool if you look.

    It would also help if you knew ISO-8601 temporal formats instead of local dialect.

     


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Tuesday, November 30, 2010 4:52 AM
  • Sorry Bineesh, I don't agree with you.

    Using CURSORS is the worst idea, and should be left only for complex logic & calculations, specially within stored-procedures, where you've to apply hundreds of business logic for each record.

     

    Solutions proposed by Naomi & Liquidloop are d best ones and should be followed in practice.

    For simple problems it is best to to solved them within a single SQL statement, cursors use should be avoided.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Tuesday, November 30, 2010 4:52 AM
  • Jiya,

    The query that I provided is just to show how it can be done in tsql, however I strongly agree with Naom & Celko. This is best done in your reporting tool. And find out what Database Normalization means.

    Tuesday, November 30, 2010 5:45 AM
  • Hi Liquidloop

    Your Question.   "I don't understand why the last record should show State, City etc details. Try the following query."

    Answer: In last record even if Equiry No is 30, but state,city and order no is different.  (One Enquiry can have multiple Orders from different city of same company)

    30 ,              YYYY ,        MP,  Bhopal,         45 ,     1/1/2005,       Item10,   2
    30,               YYYY ,        MP,  Bhopal,         45 ,     1/1/2005,       Item11,   1
    30,               YYYY ,        MH,  Pune,            46 ,     1/1/2005,       Item11,   1 

    Regards
    Jiya

    Wednesday, December 1, 2010 4:19 AM
  • Hi Celko,

    I somewhat agree with you.  The Need is to display this data in GridControl and not as a Report.  Users will futher do filtering based on their needs and export it to excel.  

    I prefer, to offload task on server if it can do by some command, because servers are best for these kind of jobs.  then why to write another 100 lines of code just to put spaces , test code and also maintain it.

    Doing this in Query itself is good for my needs and above reasons.

    Regards
    Jiya.

    Wednesday, December 1, 2010 4:26 AM
  • Hi LiquidLoop,

    Thanks for the query.   I am trying to understand your query, but for time being following little modification seems working for me

    (

     

    SELECT
    (ROW_NUMBER() OVER(PARTITION BY EnquiryNo,ClientName,State,City,OrderNo ORDER BY EnquiryNo, ClientName)) [RR], *
    FROM #temp) SUBQUERY1

    I would be gald if you can little explain , how query works.

    I have my comments and reasons for "Celko" added above somewhere in this forum.

    Regards
    Jiya.

     

     

    Wednesday, December 1, 2010 4:36 AM