CODEWITHSUNDEEP
sqlsql-server
Krishna
Krishna

Reputation: 1

SQL Server displaying two rows data to a single row

I have a table with columns ID, Address and Type and data as follows

1 ADD1 1
1 Add2 2
2 Add1 1
2 Add2 2

Using a query how can I get the data as follows:

Columns

ID Addess1 Address2

1 Add1 Add2
2 Add1 Add2

Using the third column Type,

Please suggest how to write the query for the above requirement.

Upvotes: 0

Views: 1235

Answers (4)

Srinivasa Reddy R
Srinivasa Reddy R

Reputation: 11

SELECT COLUMNID, MAX(CASE TYPE WHEN 1 THEN ADDRESS1 ELSE '' END) AS ADD1, MAX(CASE TYPE WHEN 2 THEN ADDRESS1 ELSE '' END) AS ADD2 FROM MY_TABLE GROUP BY COLUMNID

Upvotes: 0

user1709803
user1709803

Reputation:

It depends. If you are using just SQL to query the server and get the data then you would use something like the SQL code provided in @neoistheone answer

If you are accessing the database from within a program written in VB.NET for example you could use a stored procedure and pass the TYPE as a parameter. See sample code below:

ResetParameters() CreateParameter("Type", ADODB.DataTypeEnum.adVarChar,ADODB.ParameterDirectionEnum.adParamInput, 10, Type) RunSP("spx_Select_Data", "GlobalRS")

And then loop to display the data like so:

If not GlobalRS.EOF then Grid.Rows.Add(CFN(GlobalrS("ID").Value), CFN(GlobalrS("Address").Value)) GlobalRS.MoveNext(); End If

Hope this makes sense

Upvotes: 0

Xelom
Xelom

Reputation: 1625

Select ColumnID, 
CASE Type WHEN 1 THEN Address1 ELSE '' END AS Add1,
CASE Type WHEN 2 THEN Address1 ELSE '' END AS Add2
FROM MyTable

Upvotes: 1

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

One option might be like this:

SELECT ID, CASE WHEN Type = 1
                THEN Address
                ELSE '' END AS Add1,
           CASE WHEN Type = 2
                THEN Address
                ELSE '' END AS Add2
FROM tbl

Another option might be something like this:

SELECT ID, Address AS Add1, '' AS Add2
FROM tbl
WHERE Type = 1
UNION
SELECT ID, '' AS Add1, Address AS Add2
FROM tbl
WHERE Type = 2

Upvotes: 1

Related Questions