CODEWITHSUNDEEP
sql
Blah Blah
Blah Blah

Reputation: 49

Adding column using SQL select

My table currently looks like this

Table1

Is there a way that I can create a select statement that puts the table like this without changing the original table?

Table2

I currently have SELECT ID, Word FROM Table WHERE Lang = 1 OR Lang = 2

Upvotes: 0

Views: 73

Answers (2)

devil_coder
devil_coder

Reputation: 1135

I created the temp table based around your table and enhanced @ImranRashid query below

create table #Language 
(
Id Int,
Lang Int,
word NVARCHAR(100)
)

Insert INTO #Language 
VALUES(1,1,'Hello');
Insert INTO #Language 
VALUES(1,2,'Bonjour');
Insert INTO #Language 
VALUES(2,1,'Thank you');
Insert INTO #Language 
VALUES(2,2,'Merci');

Now run below query to self join but make sure lang id is selected differently 

SELECT 
g1.ID, g1.word as English, g2.word as French 
FROM 
#Language g1, #Language g2 
WHERE 
g1.ID = g2.Id AND g1.Lang = 1 AND g2.Lang=2;

ID English French

1 Hello Bonjour

2 Thank you Merci

Now to test this with another word run below insert query 

Insert INTO #Language 
VALUES(3,1,'Hi');
Insert INTO #Language 
VALUES(3,2,'Salut');

Run above query again Result below

ID English French

1 Hello Bonjour 2 Thank you Merci 3 Hi Salut

Upvotes: 0

Imran Rashid
Imran Rashid

Reputation: 379

Yes this is a clear case of self join. I am assuming that your table name is "greetings". You can do it like this:

SELECT 
g1.ID, g1.word as English, g2.word as French 
FROM 
greetings g1, greetings g2 
WHERE 
g1.ID = g2.Lang;

Upvotes: 1

Related Questions