Converting rows into columns

• Mohammed harris

  Newbie

  Points: 3

  More actions

  August 22, 2006 at 1:20 am

  #76571

   

  Hi All,

  Here I have two tables with relations.

  This is the query I wrote:

  SELECT DISTINCT Address_1.Building AS Expr1, Address_1.City AS Expr2, Address_1.Id AS Expr3, Contact_1.Info

  FROM Address AS Address_1 INNER JOIN Contact AS Contact_1 ON Address_1.Id = Contact_1.AddressId

  Where Contact_1.AddressId = 8

  and Iam getting the result like,

  Expr1        Expr2        Expr3        Info address1     address2     address3     11111 address1     address2     address3     22222 address1     address2     address3     33333 

  I want the info details in a single Row not in three rows (ike pivot)

  Expr1        Expr2        Expr3        Info1   Info2  Info3 address1     address2     address3     11111   22222  33333 

   

  This is the table structure.

  CREATE TABLE [dbo].[Contact]([AddressId] [bigint] NOT NULL,      [Id] [bigint] IDENTITY(1,1) NOT NULL, [Info] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TypeId] [bigint] NOT NULL,CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

   CREATE TABLE [dbo].[Address]( [Building] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,  [City] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,  [Country] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Id] [bigint] IDENTITY(1,1) NOT NULL,[PartyId] [bigint] NOT NULL, [PostalCode] [bigint] NULL, [State] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Street] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Suburb] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TypeId] [bigint] NOT NULL, CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

   

  Thanks

  Naresh

   

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  August 22, 2006 at 3:26 am

  #656074

  You are selecting DISTINCT over INFO columns. And that info is not equal for the same person and same building. That's why you get two rows for Contact 8.

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Bhushan

  Default port

  Points: 1427

  More actions

  August 24, 2006 at 10:48 am

  #656656

  If you are using SQL 2005 and explore PIVOT operator