CODEWITHSUNDEEP
sqlsql-server
AWippler
AWippler

Reputation: 417

SQL Server : return 3 items

Short version: I need to return a query with 3 items from another table and adding it to the existing table.

Long version:

Table A contains the following information:

| ID | Name | Date |  Comment  |
--------------------------------
| 1  | AJ   | 9/11 | Howdy     |
| 2  | AW   | 9/13 | Hi        |
| 3  | AK   | 9/15 | Aloha     |
| 4  | AW   | 9/15 | Hello     |
| 5  | AJ   | 9/18 | Greetings |

I need Table B to resemble:

| ID |  Comment  |  Comment2  |  Comment3  |
--------------------------------------------
| 1  | Howdy     |  Aloha     |  Greetings |

I am running 

SELECT TOP 3 * 
FROM a 
WHERE Name IN ('AJ','AK')

but that makes Table B appear like:

| ID | Name | Date |  Comment  |
--------------------------------
| 1  | AJ   | 9/11 | Howdy     |
| 3  | AK   | 9/15 | Aloha     |
| 5  | AJ   | 9/18 | Greetings |

Is it even possible to get what I want?

Upvotes: 0

Views: 198

Answers (3)

Jayaram
Jayaram

Reputation: 839

I don't know if this will work, but i'm just throwing the idea here. Let me know if it works! 

   Insert into B Values (
    ( SELECT TOP 3 comment FROM a WHERE Name IN ('AJ','AK') limit 1 ),
    ( SELECT TOP 3 comment FROM a WHERE Name IN ('AJ','AK') limit 1,1 ),
    ( SELECT TOP 3 comment FROM a WHERE Name IN ('AJ','AK') limit 2,1 )
    )

This is for MySQL. please change accordingly for MSSQL Server

Upvotes: -1

code save
code save

Reputation: 1106

Please try this , it helpful to you

  select b.id, b.comment as comment
, (select comment from ##temp1 where id = b.id+2 )  as comment1
, (select comment from ##temp1 where id = b.id+4 )  as comment2
  from  ##temp1 b  where b.id=1

Upvotes: 1

Lock
Lock

Reputation: 5522

Not entirely sure what you are after as you have id's for each comment, then your output has a single row with an id (where does this id come from for your output?) but this may be able to be expanded upon:

SELECT
  [1] AS COMMENT1,
  [2] AS COMMENT2,
  [3] AS COMMENT3
FROM
  TABLE_A
PIVOT (MAX(COMMENT) FOR id IN ([1],[2],[3])) AS PVT

Upvotes: 1

Related Questions