[RESOLVED] SQL statement pls help

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


Attachment 65410

Thread Moved

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

Code:

---

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.

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

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.

Code:

---

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

---

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.

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

heres the code :

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!