-2

I have two tables:

Tab1(columns containing only id )

and 

Tab2 (columns containing id and service)

I joined two tables:

select tab1.id,tab2.service 
from tab1 
inner join tab2 on tab1.id=tab2.id

My problem is i have id as 1 for that id i have two services cash and deposit. for Eg. am getting after joining tables 

id service 
1 cash
1 deposit

but i want as single row as 

id service
1 cash/deposit

how to concatenate that ??

larsts
  • 451
  • 5
  • 12
Padhu
  • 97
  • 3
  • 10

2 Answers2

0

From the limited information you have provided I understand your questions as follows:

You have two tables, Service and ID.
These both have a primary key (ID) that matches the other table.
One table has a value column (cash).
The other table has a value column (deposit).
You wish to display both value columns together in one row. 

SELECT Service.ID, ID.ID, Service.Cash, ID.Deposit
FROM Service JOIN ID ON Service.ID = ID.ID

Please correct me if this is wrong or provide more information to allow me to review my answer.

Based on your latest information:

SELECT Table1.ID, Table2.ID, Table1.Service, Table2.Service
FROM Table1 JOIN Table2 ON Table1.ID = Table2.ID

Again, please provide the requested information for clarity.

A. Greensmith
  • 355
  • 1
  • 8
  • Hi,Thanks for your response.id and service is columns , after joining two tables i get two columns as id and service ..Eg. id -->1, service-->cash and id ---1> ,service--->deposit (this is wat i get after joining two tables ) . i want as id-->1 and service -->cash/deposit @A. Greensmith – Padhu Sep 14 '15 at 14:22
  • Please draw out your tables. When you inner join on the ID (primary key) of two tables, if they match then you will get both 'service' columns (providing you select them). I think your problem might be ambiguous column names. **Please provide me your table structure and SQL query so that I can fix it.** – A. Greensmith Sep 14 '15 at 14:28
  • please see the edited question of mine above . i changed for you @A.Green – Padhu Sep 14 '15 at 14:46
0

Based on your revised question, this will hopefully give you what you are after:

Create Sample Tables: 

CREATE TABLE Table1 (ID INT)
CREATE TABLE Table2 (ID INT, Service NVARCHAR(MAX))

Insert Sample Data:

INSERT INTO Table1 VALUES ('1')
INSERT INTO Table1 VALUES ('2')
INSERT INTO Table1 VALUES ('3')
INSERT INTO Table2 VALUES ('1', 'Cash')
INSERT INTO Table2 VALUES ('1', 'Deposit')
INSERT INTO Table2 VALUES ('2', 'Cash')
INSERT INTO Table2 VALUES ('2', 'Deposit')
INSERT INTO Table2 VALUES ('3', 'Cash')
INSERT INTO Table2 VALUES ('3', 'Deposit')

Two Columns: 

SELECT Table1.ID, MAX(CASE Table2.Service WHEN 'Cash' THEN Table2.Service END), MAX(CASE Table2.Service WHEN 'Deposit' THEN Table2.Service END)
FROM Table1 JOIN Table2 ON Table1.ID = Table2.ID
GROUP BY Table1.ID

Concatenated:

SELECT Table1.ID, CONCAT(MAX(CASE Table2.Service WHEN 'Cash' THEN Table2.Service END), '/', MAX(CASE Table2.Service WHEN 'Deposit' THEN Table2.Service END))
FROM Table1 JOIN Table2 ON Table1.ID = Table2.ID
GROUP BY Table1.ID

Please mark this as answer if it answers your question!

A. Greensmith
  • 355
  • 1
  • 8
  • Hi ,thanks for your response . and i need to mention that service does not contain only cash and deposit .there will be many services.so its not possible to put case statement . – Padhu Sep 15 '15 at 04:10
  • Padhu, have you even tried this? The CASE statement looks for an exact match. It doesn't matter what other services are in that column it will only use 'Cash' or 'Deposit'. If you wish to add more services then expand the query or use an IN statement to list your possible results. – A. Greensmith Oct 20 '15 at 15:14