[RESOLVED] SQL statement pls help

[hoobas20]

I have a problem on how to filter this table and have the output based on their ID. Please help thanks

[RobDog888]

Thread Moved

[kevchadders]

To get the output to match on their ID's, pass the ID in the WHERE Clause. (Bases on T-SQL)

SELECT [Name]
FROM TableA
WHERE [id] = 1 
-- Change the number in the where clause to get different names

If you are running this SQL from a program e.g. VB6, then you can pass the id as a parameter to the SQL so that you can return different results.

ps. Not sure if that was what you where after... if it isnt, could you explain more what you want.

[hoobas20]

all i want is to display all that 3 id in 3 columns inonly 1 table..exactly what my example is.. Any idea? thanks

[kevchadders]

This code will split them into 3 columns, but NULL's will be present. There are other ways i could of done it will NULLs showing, but i though giving you the example with a temp table as it might be usefull for you.

It is very difficult to return the columns the way you want (without the NULL's appearing) as they vary in length.

CREATE TABLE #name_temps
	(
		field1	Varchar ( 50 ),
		field2	Varchar ( 50 ),
		field3	Varchar ( 50 )                
	)

-- Insert for ID = 1
INSERT INTO #name_temps (field1)
	SELECT name
	FROM TableA WITH (NOLOCK) 
	WHERE [id] = 1

-- Insert for ID = 2
INSERT INTO #name_temps (field2)
	SELECT name
	FROM TableA WITH (NOLOCK) 
	WHERE [id] = 2

-- Insert for ID = 3
INSERT INTO #name_temps (field3)
	SELECT name
	FROM TableA WITH (NOLOCK) 
	WHERE [id] = 3

-- Select the data
SELECT DISTINCT * FROM #name_temps

-- Drop table after you have finished with it
DROP TABLE #name_temps

[FunkyDexter]

What you're trying to do is create a pivot table. The bad news is that there's no standard SQL to do this. The good news is that most implementations of SQL have their own syntax for doing it. If you let us know what database you're using (including the version) the chances are someone will be able to tell you how to do it.

[hoobas20]

thanks guys! i already solved it.. using case statement :

heres the code :

select case when id = 1 then name end name1, case when id = 2 then name end name2, case when id = 3 then name end name3  from TableA

Thanks guys!