locked
T-SQL RRS feed

  • Question

  • Hi All,

    I had data in a table as below

    id amt   aadhaarno 

    1  10000  

    2  5000

    1  10000

    2  5000    4789633

    I want the output as below.if aadhar is present then aadhar_status should be Y or NO

    id  amt     aadhar   aadhar_status

    1  20000                  N              

    2  10000   4789633   Y

    Thanks in advance


    • Edited by Raghunadhan Saturday, August 24, 2019 1:39 PM
    Saturday, August 24, 2019 1:38 PM

Answers

  • Try this version, using a derived table:

    SELECT
    	  id
    	, SUM(amt) AS amt
    	, aadhar_status
    FROM (
    	SELECT
    		  id
    		, amt
    		, CASE WHEN EXISTS(SELECT 1 FROM dbo.YourTable AS b WHERE a.id = b.id AND b.aadhaarno IS NOT NULL) THEN 'Y' ELSE 'N' END AS aadhar_status
    	FROM dbo.YourTable AS a
    ) AS A
    GROUP BY
    	  id
    	, aadhar_status


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, August 24, 2019 4:18 PM
    Answerer

All replies

  • If the aadhaarno values are NULL, you could use the CASE expression like below.

    SELECT
    	  id
    	, SUM(amt) AS amt
    	, CASE WHEN aadhaarno IS NULL THEN 'N' ELSE 'Y' END AS aadhar_status
    FROM dbo.YourTable
    GROUP BY
    	  id
    	, CASE WHEN aadhaarno IS NULL THEN 'N' ELSE 'Y' END;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, August 24, 2019 1:57 PM
    Answerer
  • Hi Dan Guzman,

    Thanks for your reply

    Returning 3 rows

    Saturday, August 24, 2019 2:12 PM
  • Hi Dan Guzman,

    Output should be as below

    ---------------------------

    id  amt     aadhar   aadhar_status

    1  20000                  N              

    2  10000   4789633   Y

    Saturday, August 24, 2019 3:55 PM
  • Try this version, using a derived table:

    SELECT
    	  id
    	, SUM(amt) AS amt
    	, aadhar_status
    FROM (
    	SELECT
    		  id
    		, amt
    		, CASE WHEN EXISTS(SELECT 1 FROM dbo.YourTable AS b WHERE a.id = b.id AND b.aadhaarno IS NOT NULL) THEN 'Y' ELSE 'N' END AS aadhar_status
    	FROM dbo.YourTable AS a
    ) AS A
    GROUP BY
    	  id
    	, aadhar_status


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, August 24, 2019 4:18 PM
    Answerer
  • Hi Dan Guzman,

    Thanks for your help and its working. Can you explain how does it works. What is the difference in using only case and using case with exists operator in the above query.

    Thanks


    • Edited by Raghunadhan Sunday, August 25, 2019 4:54 AM
    Sunday, August 25, 2019 4:48 AM
  • Can you explain how does it works. What is the difference in using only case and using case with exists operator in the above query.

    The CASE expression in the first query checks the aadhar value of only the current row so grouping by that CASE expression results in 2 rows for the same id when different rows contain a mix of NULL and NOT NULL values. This was an oversight on my part because I didn't consider your actual need was to check if any rows with the same id has a NOT NULL aadhar value and I didn't bother to test the query.

    The derived table CASE expression in the second query uses EXISTS to determine if any rows with the same id have a NOT NULL aadhar value so the same aadhar_status value is assigned to all rows with the same id. The outer query groups by id and aadhar_status (which is the same for each id) so the end result is one row per id along with the amt SUM.

    For future questions, it is in your best interest to include minimal schema and data in the form of SQL scripts like below rather than as text. This will facilitate better and faster answers from your peers whom are sometimes too lazy to create the scripts themselves in order to provide a tested solution.

     
    CREATE TABLE dbo.YourTable(
    	  id int 
    	, amt int
    	, aadhaarno  int
    );
    INSERT INTO dbo.YourTable VALUES
    	 (1, 10000, NULL)
    	,(2, 5000, NULL)
    	,(1, 10000, NULL)
    	,(2, 5000, 4789633);


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Sunday, August 25, 2019 11:24 AM
    Answerer
  • Hi Dan Guzman,

    Thanks for your explanation and will include scripts as above for  future questions.

    Thanks

    Sunday, August 25, 2019 11:35 AM
  • Hi Dan Guzman,

    Thanks for your explanation and will include scripts as above for  future questions.

    Thanks

    Hi Raghunadhan,

    I am so glad to hear that you have resolved your issue. Please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 26, 2019 1:29 AM