Comments by HarshadaBS (Top 33 by date)

One more thing is pivot is used when we want to convert rows into columns. It was my mistake that I didn't mention that, my tables come from database. I didn't created it. But now I have changed my question. But it doesn't include tables now.

Yes. You are R8. Your query helps in advance level. But I will try it and get back to you soon.

It's really nice to have this query. I will try it too and reply you back soon. Empty cells are present because I put syntax like (else ''). And it was the requirement to display empty cells if (name) is not in ( customer, client, supplier). In my database some names put under these three relationships and some names are not in any relationship. That's why some cells are empty.

This is not exactly true. Whatever I ask here is for dummy data. I already tried some solutions by my own. When I was unable to find exact syntax I ask here. In reality it's a small part of big query. But as I said I am new to sql, sometimes I find it difficult. As you suggest I have changed my question.

Yes. Perfect

I want distinct record of each unique name and column values of supplier, client and customer should be like output given by your query i.e. solution 1

I have tried this 'group by con.companyname'. but it's not working.

as you can see isclient , issupplier, iscustomer columns are not from database. Please try to understand above query. and let me know how to apply your answer

yes its working.
but my source-table is coming from database. i am giving my query so that you can understand.
Your query is perfect but how to apply to below query.

SELECT DISTINCT Con.CompanyName AS Contact,
'Y' AS IsClient,
'' AS IsCustomer,
'' AS IsSupplier

FROM contacts con
INNER JOIN Countries Cont ON Con.CountryID=Cont.CountryID
INNER JOIN ContactRelations CRes ON Con.ContactID=CRes.ContactID
INNER JOIN ContactRelationTypes CRel ON CRes.ContactRelationTypeID=CRel.ContactRelationTypeID
WHERE CRel.ContactRelationTypeID=2 -- 2 Client

UNION
SELECT DISTINCT Con.CompanyName AS Contact,
'' AS IsClient,
'Y' AS IsCustomer,
'' AS IsSupplier

FROM contacts con
INNER JOIN Countries Cont ON Con.CountryID=Cont.CountryID
INNER JOIN ContactRelations CRes ON Con.ContactID=CRes.ContactID
INNER JOIN ContactRelationTypes CRel ON CRes.ContactRelationTypeID=CRel.ContactRelationTypeID
WHERE CRel.ContactRelationTypeID=3 -- 3 Customer

UNION

SELECT DISTINCT Con.CompanyName AS Contact,
'' AS IsClient,
'' AS IsCustomer,
'Y' AS IsSupplier

FROM contacts con
INNER JOIN Countries Cont ON Con.CountryID=Cont.CountryID
INNER JOIN ContactRelations CRes ON Con.ContactID=CRes.ContactID
INNER JOIN ContactRelationTypes CRel ON CRes.ContactRelationTypeID=CRel.ContactRelationTypeID
WHERE CRel.ContactRelationTypeID=8 -- 8 Supplier

No. This query does not give R8 answer. I have already tried this. Need output in one row only. And all columns should display with their values as shown in my output

Okay. I am new to CodeProject. But it is very nice experience to know more about coding

That's what I need. Thank you so much for helping.

Now using above code only how can we convert null values with 0. Can you share syntax?

this is your 1st code
------------------------
DROP TABLE IF EXISTS #Demo;
CREATE TABLE #Demo(Client VARCHAR(50), Country VARCHAR(50),
InvoiceAmount DECIMAL(5,2))
INSERT INTO #Demo
VALUES
('Raja','India',100.00),('Ajit','Begium',200.00),('Sukanya','China',300.00),
('Neha','India',600.00),('Raja','India',300.00),('Ajit','Begium',800.00),
('Sukanya','China',700.00),('Neha','India',600.00),('Raja','Belgium',50.00),
('Neha','France',130.00);

DECLARE @QUERY AS NVARCHAR(MAX);
DECLARE @Col1 AS NVARCHAR(50)='Country'; --'CLIENT'
DECLARE @Col2 AS NVARCHAR(50)='CLIENT'; --'Country';
DECLARE @Col3 AS NVARCHAR(50)='InvoiceAmount';
DECLARE @InputTable AS NVARCHAR(50)='#DEMO';
DECLARE @PivotRows AS NVARCHAR(MAX);
DECLARE @Pvtab TABLE(Cols varchar(max));
DECLARE @Pvtcol Nvarchar(max);

SET @PivotRows =N'SELECT STUFF((SELECT DISTINCT '',''+QUOTENAME('+@Col2+',''['')
FROM ' + @InputTable + ' AS C
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'

INSERT INTO @Pvtab(Cols) EXEC(@PivotRows);
SELECT @Pvtcol=Cols FROM @Pvtab;
---print @pvtcol
SET @QUERY = N'SELECT * FROM
(
SELECT DISTINCT '+@Col1+','+@Col2+','+@col3+' FROM ' + @InputTable + '
)AS Te
PIVOT (
SUM(['+@Col3+']) FOR '+@Col2+' IN ('+@Pvtcol+')
)AS PIV'

EXEC(@QUERY)
------------------------------------------------
Using This code only(means only use table variables and column variables) (do not use name of columns directly)(columns and table should be dynamic) I want the output like below
Country Ajit Neha Raja Sukanya
1 Begium 1,000.00 0.00 0.00 0.00
2 Belgium 0.00 0.00 50.00 0.00
3 China 0.00 0.00 0.00 1,000.00
4 France 0.00 130.00 0.00 0.00
5 India 0.00 600.00 400.00 0.00

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

This query returns null as 0.

Sorry this was the output of previous query.

after running above code i am getting this output. Am I doing anything wrong? What should I do?
-----------------------------------------------------------------
(No column name)
cast([Begium] AS Varchar(20)) AS [Begium],cast([India] AS Varchar(20)) AS [India]
-----------------------------------
(No column name)
[Begium], [India]

I mean to say... In your query, how can I convert null values with 0? Please give me syntax.

Your query gives correct output. I don't want to change the output. I just want to replace all null values with 0. That's what I need and want syntax to convert null values with 0. But use above code only. Do not write separate syntax for isnull.

Can you share the syntax to avoid Null values?

/*Something like this...But this query is not right*/
SET @PivotRows =N'SELECT STUFF((SELECT DISTINCT '',ISNULL('+QUOTENAME('+@Col2+',''['')+',0.0)AS'+QUOTENAME('+@Col2+',''['')+''
FROM ' + @InputTable + ' AS C
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'

To avoid Null values and replace it with '0' then what will be the syntax? I am trying but it gives syntax error.

This Query Works Properly

Before trying this query, I want to ask you that, can you explain why did you use temporary table (#Demo), instead of table variable(ex. @Table)?