Click here to Skip to main content
15,171,873 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to convert multiple rows into single row
Where all column values should display in one
Row only.

What I have tried:

I tried to use distinct record.
Posted
Updated 8-Dec-17 1:17am
v6

WITH CTE AS (SELECT DISTINCT Con.CompanyName AS Contact, 
CASE WHEN CRel.ContactRelationTypeID= 2 then 'Y' 
     WHEN CRel.ContactRelationTypeID in(3,8) then ' ' END  AS IsClient,
CASE WHEN CRel.ContactRelationTypeID=3 THEN 'Y'
     WHEN CRel.ContactRelationTypeID in (2,8) THEN ' ' END  AS IsCustomer,
CASE WHEN CRel.ContactRelationTypeID =8  THEN 'Y' 
     WHEN CRel.ContactRelationTypeID in(2,3)  THEN ' ' END 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 in(2,8,3) )

SELECT Contact,
       MAX(IsClient) AS Customer ,
       MAX(IsCustomer) AS Client,
       MAX(IsSupplier)AS Supplier
 FROM CTE 
     GROUP BY Contact;
   
v5
Comments
HarshadaBS 6-Dec-17 6:26am
   
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
Santosh kumar Pithani 6-Dec-17 6:39am
   
Query is updates check!
HarshadaBS 6-Dec-17 7:13am
   
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
HarshadaBS 6-Dec-17 7:16am
   
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
Santosh kumar Pithani 6-Dec-17 7:23am
   
-- you can do group by direction by table column
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 GROUP BY Con.CompanyName
HarshadaBS 6-Dec-17 7:29am
   
I have tried this 'group by con.companyname'. but it's not working.
HarshadaBS 6-Dec-17 7:37am
   
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
Santosh kumar Pithani 6-Dec-17 7:43am
   
query is updated check and let me know!:)
HarshadaBS 6-Dec-17 8:23am
   
Yes. Perfect
Santosh kumar Pithani 6-Dec-17 22:58pm
   
Improve your question according to updated solution, your must be clear before posting your question.This is second time you did like this don't repeat once more further your just expecting perfect solutions without doing any effort.
HarshadaBS 8-Dec-17 6:24am
   
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.
Santosh kumar Pithani 11-Dec-17 0:01am
   
update union query in "What I have tried:"
You need to create Stored procedure for this, not possible through one single query

SQL
CREATE PROC spGetDistinctResult    
AS    
SET NOCOUNT ON;  
DECLARE @tblResult TABLE (ID INT  IDENTITY(1, 1),NAME VARCHAR(10),Customer VARCHAR(10),Client VARCHAR(10),Supplier VARCHAR(10))
DECLARE @tblName TABLE (ID INT  IDENTITY(1, 1),NAME VARCHAR(10))
DECLARE @Customer AS  VARCHAR(10)
DECLARE @Client AS VARCHAR(10)
DECLARE @Supplier AS VARCHAR(10)
DECLARE @TtlRows AS INT

INSERT INTO @tblName(NAME)
SELECT distinct Name FROM TABLE1 ORDER BY Name

SET @TtlRows=0

While (Select Count(*) From @tblName ) > 0
Begin
    @TtlRows = @TtlRows + 1
     
      	 
     SELECT @Name=	Name FROM @tblResult WHERE ID=@TtlRows
	 SET @Customer = ""
	 SET @Client = ""
	 SET @Supplier = ""
	 
	 SELECT @Customer=	Customer FROM @tblResult WHERE ID=@TtlRows AND RTRIM(LTRIM(Customer))<>""
	 SELECT @Client=	Client FROM @tblResult WHERE ID=@TtlRows AND RTRIM(LTRIM(Client))<>""
	 SELECT @Supplier=	Supplier FROM @tblResult WHERE ID=@TtlRows AND RTRIM(LTRIM(Supplier))<>""
	 	 
     INSERT INTO @tblResult
	 SELECT @Name,@Customer,@Client,@Supplier
	 

End

select * from @tblResult
   
Comments
CHill60 6-Dec-17 9:07am
   
And yet Solutions 1 and 3 have done it with a single query ... and more importantly without a loop! There is very rarely any situation where you actually need to use a loop in a set-based language
HarshadaBS 9-Dec-17 9:35am
   
Yes. You are R8. Your query helps in advance level. But I will try it and get back to you soon.
You are trying to solve a wrong problem. Have you ever wondered whey your table has so many empty cells? It is a sure sign of inadequate database design. Re-look into the database design, check out this example:
CREATE TABLE tablename
(
field1 varchar(255),
field2 varchar(255),
PRIMARY KEY (field1, field2)
)

INSERT INTO tablename
VALUES
('ABC', 'Customer'),
('ABC', 'Client'),
('ABC', 'Supplier'),
('DEF', 'Customer'),
('DEF', 'Client'),
('GHI', 'Client');

SELECT field1, [Customer], [Client], [Supplier] 
FROM
(SELECT field1, field2 FROM tablename) as src
PIVOT
(
   COUNT(field2) FOR field2 IN ([Customer], [Client], [Supplier] )
) AS output
or the live demo at [^]
   
v2
Comments
HarshadaBS 9-Dec-17 9:30am
   
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.
HarshadaBS 9-Dec-17 9:41am
   
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900