SQL Return 1 or distinct values from multiple columns

Question

My goal is to get the customer balance report but I am stuck in my query where I can get the doc type of a certain column. I have my query

SELECT
ROW_NUMBER() OVER( ORDER BY DR.id ) AS 'NO.' ,          
CONVERT(VARCHAR(13), CAST(DR.doc_date AS DATE), 100) AS 'DOC DATE',
SR.net_total,
SR.dell_col_charge,
SR.CDW,
sp.amount
FROM 
[dbo].[doc_customer]                DC
LEFT JOIN   [dbo].[doc_rent]            DR ON DR.doc_sourced_customer_id = DC.id
LEFT JOIN   [dbo].[slip_rent]           SR ON SR.doc_sourced_doc_rent_id = DR.id
LEFT JOIN   [dbo].[slip_rent_payment]   SP ON SP.doc_sourced_rent_id     = DR.id

as u can see in my query it returning the net total, charge, cdw, and amount but my goal is to show them in 1 column in a different rows. u can see on the image I attached that's my goal of my query.

anyone have the idea ? thanks.

Gordon Linoff · Accepted Answer · 2017-10-18 11:55:50Z

2

I like to use apply for unpivoting:

SELECT ROW_NUMBER() OVER( ORDER BY DR.id ) AS [NO.] ,          
       CONVERT(VARCHAR(13), CAST(DR.doc_date AS DATE), 100) AS [DOC DATE],
       v.*
FROM [dbo].[doc_customer] DC LEFT JOIN
     [dbo].[doc_rent] DR
     ON DR.doc_sourced_customer_id = DC.id LEFT JOIN
     [dbo].[slip_rent] SR
     ON SR.doc_sourced_doc_rent_id = DR.id LEFT JOIN
     [dbo].[slip_rent_payment] SP
     ON SP.doc_sourced_rent_id = DR.id OUTER APPLY
     (VALUES ('net_total', SR.net_total),
             ('dell_col_charge', SR.dell_col_charge),
             ('CDW', SR.CDW),
             ('amount', sp.amount)
     ) v(which, val);

answered Oct 18, 2017 at 11:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

FritsJ Over a year ago

Thanks ! I have now the idea. But can I ask something why it's returning string like this "----" below of the name of the columns ? I'm unfamiliar with that.

Gordon Linoff Over a year ago

That is how the application you are using for querying works. SQL Server Management Studio does this when you put the results in text mode rather than in a grid.

Collectives™ on Stack Overflow

SQL Return 1 or distinct values from multiple columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related