none
Convert set of rows into column RRS feed

  • Question

  • Hi, I have a table with lots of rows like below format

    Column_name Value
    Company Name ANNAPURNA  BIO-VED  PVT  LTD
    Contact  Name SHRI  G.R.  KARUNAKAR
    Designation  MANAGING  DIRECTOR
    Address 16-10-227,  ANNAPURNA  NILAYAM,APARTMENTS,  OLD  MALAKPET,HYDERABAD,  Andhra  Pradesh
    Phone  040-24243338
    Fax 9.14024E+11
    email  karthikgudi@annapurnabioved.com
    Website  http://www.annapurnabioved.com
    Business Activities Ayurvedic  Medicines
     NULL  NULL
    Company Name ANSAR  CHEMICAL
    Contact  Name SHRI  ATTIQ  UR  REHMAN
    Designation  PARTNER
    Address  LODHI  SARAI,  SAMBHAI,MORADABAD-,  Uttar  Pradesh
    Phone  05923-231551
    Fax 9.15921E+11
    email info@ansarchemical.com
    Website  http://www.ansarchemical.com
    Business  Activities  Menthol  Crystal,  Dementolised,Peppermint  Oil,  Essential  Oil,ANSAR  INDUSTRIES
       

    Output will be

    Company Name!! Contact  Name!! Designation!! Address!! Phone!! Fax!! email!! Website!! Business Activities!!
    ANNAPURNA  BIO-VED  PVT  LTD SHRI  G.R.  KARUNAKAR MANAGING  DIRECTOR VISHWAM',8/B,POSTAL COLONY,CHEMBUR MUMBAI,  Maharashtra 9820613706 91-80-26632414 tricap@md3.vsnl.net.in, www.balajipharma.com Ayurvedic  Medicines
    ANSAR  CHEMICAL SHRI  ATTIQ  UR  REHMAN PARTNER  #  No.2,  3rd  Cross,  Vijayashree Layout,  B.g.road,  Bangalore Ayurvedic  Medicines

    Please help.

    Thanks in advance

     

    Friday, January 24, 2020 9:49 AM

All replies

  • For this you can use the PIVOT function: FROM - Using PIVOT and UNPIVOT

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, January 24, 2020 9:58 AM
    Moderator
  • Dear I using pivot fuction for this. But it retriving only 1 rows each time.

    This is the below query I use

    SELECT *
    FROM  
    (SELECT  [Column_name]
          ,[Value] 
        FROM [demo].[dbo].[Pharma-soham-16-50]) AS SourceTable  
    PIVOT  
    (  
     max([Value])
    FOR [Column_name] IN ([Company Name],[Contact  Name],Designation,Address,Phone,Fax,email,Website,[Business Activities])  
    ) AS PivotTable;

    How I get all rows for multiple [Company Name]?

    Friday, January 24, 2020 10:05 AM
  • Please post your sample as CREATE TABLE + INSERT statements. I am not able to understand your post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, January 24, 2020 10:40 PM
  • Hi Ernald,

    Please find the Create table and insert into statement

    CREATE TABLE [dbo].[Pharma-soham-16-50_2](
    [Column_name] [nvarchar](255) NULL,
    [Value] [nvarchar](255) NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Company Name', N'ANNAPURNA  BIO-VED  PVT  LTD')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Contact  Name', N'SHRI  G.R.  KARUNAKAR')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Designation', N' MANAGING  DIRECTOR')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Address', N'16-10-227,  ANNAPURNA  NILAYAM,APARTMENTS,  OLD  MALAKPET,HYDERABAD,  Andhra  Pradesh')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Phone', N' 040-24243338')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Fax', N'9.14024E+11')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'email', N' karthikgudi@annapurnabioved.com')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Website', N' http://www.annapurnabioved.com')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Business Activities', N'Ayurvedic  Medicines')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'', N'')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Company Name', N'ANSAR  CHEMICAL')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Contact  Name', N'SHRI  ATTIQ  UR  REHMAN')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Designation', N' PARTNER')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Address', N' LODHI  SARAI,  SAMBHAI,MORADABAD-,  Uttar  Pradesh')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Phone', N' 05923-231551')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Fax', N'9.15921E+11')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'email', N'info@ansarchemical.com')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Website', N' http://www.ansarchemical.com')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Business  Activities', N' Menthol  Crystal,  Dementolised,Peppermint  Oil,  Essential  Oil,ANSAR  INDUSTRIES')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'', N'')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Company Name', N'ANSAR INDUSTRIES')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Contact  Name', N'F.J.  ZAKVI')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Designation', N' CHIEF.  EX.  PARTNER')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Address', N' 4/3367,  H.K.  STREET,ZAMPA  BAZAR,  SURAT-,  Gujarat')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Phone', N' 0261-2433522  /  2456003')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Fax', N' 0261-2456002')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'email', N' ansarind52@hotmail.com')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'Business  Activities', N'Ayurvedic  &  Herbal  Products')
    GO
    INSERT [dbo].[Pharma-soham-16-50_2] ([Column_name], [Value]) VALUES (N'', N'')
    GO

    Also, Please run the below script to see the output 

    CREATE TABLE [dbo].[OutPutTable](
    [Company Name] [nvarchar](255) NULL,
    [Contact  Name] [nvarchar](255) NULL,
    [Designation] [nvarchar](255) NULL,
    [Address] [nvarchar](255) NULL,
    [Phone] [nvarchar](255) NULL,
    [Fax] [float] NULL,
    [email] [nvarchar](255) NULL,
    [Website] [nvarchar](255) NULL,
    [Business Activities] [nvarchar](255) NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[OutPutTable] ([Company Name], [Contact  Name], [Designation], [Address], [Phone], [Fax], [email], [Website], [Business Activities]) VALUES (N'ANNAPURNA  BIO-VED  PVT  LTD', N'SHRI  G.R.  KARUNAKAR', N' MANAGING  DIRECTOR', N'16-10-227,  ANNAPURNA  NILAYAM,APARTMENTS,  OLD  MALAKPET,HYDERABAD,  Andhra  Pradesh', N' 040-24243338', 914024000000, N' karthikgudi@annapurnabioved.com', N' http://www.annapurnabioved.com', N'Ayurvedic  Medicines')
    GO
    INSERT [dbo].[OutPutTable] ([Company Name], [Contact  Name], [Designation], [Address], [Phone], [Fax], [email], [Website], [Business Activities]) VALUES (N'ANSAR  CHEMICAL', N'SHRI  ATTIQ  UR  REHMAN', N' PARTNER', N' LODHI  SARAI,  SAMBHAI,MORADABAD-,  Uttar  Pradesh', N' 05923-231551', 915921000000, N'info@ansarchemical.com', N' http://www.ansarchemical.com', N' Menthol  Crystal')
    GO
    INSERT [dbo].[OutPutTable] ([Company Name], [Contact  Name], [Designation], [Address], [Phone], [Fax], [email], [Website], [Business Activities]) VALUES (N'ANSAR INDUSTRIES', N'F.J.  ZAKVI', N' CHIEF.  EX.  PARTNER', N' 4/3367,  H.K.  STREET,ZAMPA  BAZAR,  SURAT-,  Gujarat', N' 0261-2433522  /  2456003', NULL, N' ansarind52@hotmail.com', N'NULL', N'Ayurvedic  &  Herbal  Products')
    GO

    Please help me to retrive the output like "[OutPutTable]" from the source table name as "[Pharma-soham-16-50_2]" using pivot or by any other way..

    • Edited by SouravDutta Monday, January 27, 2020 7:22 AM
    Monday, January 27, 2020 6:00 AM
  • I have some bad news for you: that is not possible with the data that you posted. There is nothing in the data that tells that ANNAPURNA  BIO-VED  PVT  LTD goes with SHRI  G.R.  KARUNAKAR and not with SHRI  ATTIQ  UR  REHMAN.
    You may object that we can tell this from the order of the rows, but a table is by definition an unordered object. So there must be one more column that ties various pieces of information together.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, January 27, 2020 10:44 PM