How to convert multiple rows into one row?

Question

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 5-Dec-17 22:57pm

HarshadaBS

Updated 8-Dec-17 0:17am

v6

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santosh kumar Pithani · Answer 1 · 2017-12-06T00:09:00

Solution 1

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;

Posted 6-Dec-17 0:09am

Santosh kumar Pithani

Updated 6-Dec-17 1:41am

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:"

RDBurmon · Answer 2 · 2017-12-06T00:46:00

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

Peter Leow · Answer 3 · 2017-12-06T02:05:00

Solution 3

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 [^]

Posted 6-Dec-17 2:05am

Peter Leow

Updated 6-Dec-17 2:10am

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.