locked
Result Giving Me Error Please Check RRS feed

  • Question

  • User2033107836 posted

    Hello

    This is my Table 1 & Table 2 data code

    USE [My_Won_Testing_DB]
    GO
    /****** Object:  Table [dbo].[Table2]    Script Date: 01/08/2018 14:45:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table2](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NULL,
    	[Qty] [varchar](50) NULL,
    	[Unit] [varchar](50) NULL,
     CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[Table2] ON
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (2, N'A', N'10,40', N'Mtr')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (3, N'B', N'5', N'Kg')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (4, N'C', N'20', N'Ltr')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (5, N'D', N'30', N'Kg')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (6, N'D', N'10', N'Kg')
    SET IDENTITY_INSERT [dbo].[Table2] OFF
    /****** Object:  Table [dbo].[Table1]    Script Date: 01/08/2018 14:45:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table1](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](250) NULL,
    	[Qty] [float] NULL,
    	[Descriptions] [varchar](50) NULL,
     CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[Table1] ON
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (8, N'A', 10, N'Welcome')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (9, N'B', 5, N'Test')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (10, N'A', 40, N'Hello')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (11, N'C', 20, N'Nice')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (12, N'D', 30, N'Good')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (13, N'D', 10, N'Awesome')
    SET IDENTITY_INSERT [dbo].[Table1] OFF
    

    My MS SQL query

    select a.Name,a.Qty,a.Descriptions,b.Unit from 
    (select * from Table1 )a
    left outer join
    (select * from Table2) b
    on a.Name=b.Name and a.Qty=b.Qty

    Error converting data type varchar to float.

    I want to join both condition Name and Qty 

    Need output like this below

    Name Qty Descriptons Units
    A 10,40 Welcome,Hello Mtr
    B 5 Test Kg
    C 20 Nice Ltr
    D 30 Good Kg
    D 10 Awesome Kg

    Give me right code based on my code

    Thanking You

    Monday, January 8, 2018 9:17 AM

Answers

  • User77042963 posted
    /****** Object:  Table [dbo].[Table2]    Script Date: 01/08/2018 14:45:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table2](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NULL,
    	[Qty] [varchar](50) NULL,
    	[Unit] [varchar](50) NULL,
     CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
     
    SET IDENTITY_INSERT [dbo].[Table2] ON
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (2, N'A', N'10,40', N'Mtr')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (3, N'B', N'5', N'Kg')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (4, N'C', N'20', N'Ltr')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (5, N'D', N'30', N'Kg')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (6, N'D', N'10', N'Kg')
    SET IDENTITY_INSERT [dbo].[Table2] OFF
    /****** Object:  Table [dbo].[Table1]    Script Date: 01/08/2018 14:45:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table1](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](250) NULL,
    	[Qty] [float] NULL,
    	[Descriptions] [varchar](50) NULL,
     CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
     
    SET IDENTITY_INSERT [dbo].[Table1] ON
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (8, N'A', 10, N'Welcome')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (9, N'B', 5, N'Test')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (10, N'A', 40, N'Hello')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (11, N'C', 20, N'Nice')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (12, N'D', 30, N'Good')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (13, N'D', 10, N'Awesome')
    SET IDENTITY_INSERT [dbo].[Table1] OFF
    
    
    ; with mycte as 
    (select  [ID], [Name], [Qtys], Unit  from [dbo].[Table2]
     cross apply(Values ( parsename(Replace(Qty,',','.'),1)),( parsename(Replace(Qty,',','.'),2)),( parsename(Replace(Qty,',','.'),3)),(parsename(Replace(Qty,',','.'),4)))  d(Qtys)
      WHERE [Name]='A' and Qtys is not null
      )
    
      ,mycte1 as 
    
      (
      Select  a.[ID], a.[Name], [Qtys], Unit,Descriptions
      From mycte m
      join   table1 a on a.Name=m.Name and Cast(a.Qty as varchar(50))= m.Qtys )
    
    
    
      ,mycte2 as (
      SELECT t1.Name,t1.Unit,
           Stuff(( SELECT ',' + Cast(t2.QTYs as varchar(5))
               FROM mycte1 t2
              WHERE t2.Name = t1.Name and  t2.Unit = t1.Unit  
              ORDER BY Qtys
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Qtys
    			,
    			 Stuff(( SELECT ',' + Descriptions
               FROM mycte1 t2
              WHERE t2.Name = t1.Name and  t2.Unit = t1.Unit  
              ORDER BY Qtys
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Descriptions
      FROM mycte1 t1  
     GROUP BY t1.Name,t1.Unit 
    
      )
    
    Select Name,	Unit,	Qtys,	Descriptions   from mycte2
      Union ALL
    select a.Name,b.Unit,  Cast(a.Qty  as varchar(50)) ,a.Descriptions  from 
    (select [ID], [Name], [Qty], [Descriptions] from Table1 )a
    left outer join
    (select [ID], [Name], [Qty], [Unit] from Table2) b
    on a.Name=b.Name and Cast(a.Qty as varchar(50))= b.Qty
    WHERE b.Unit is not null
    
    
    
    
    
    drop TABLE [dbo].[Table1], [dbo].[Table2]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 9, 2018 2:25 PM

All replies

  • User77042963 posted

    Redesign your table2 to hold single value in Qty as int not the N'10,40'.

    To work with your current design is hard for you. You need to use split function to split the value to finish the join and use for xml path to put the split values back to one place.

    Monday, January 8, 2018 3:40 PM
  • User2033107836 posted
    Hello
    Thanks for your reply

    Can you give me code base on my table data..

    Thanks
    Monday, January 8, 2018 6:24 PM
  • User77042963 posted

    It should not be this complicate. Any way, here is the query.

    /****** Object:  Table [dbo].[Table2]    Script Date: 01/08/2018 14:45:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table2](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NULL,
    	[Qty] [varchar](50) NULL,
    	[Unit] [varchar](50) NULL,
     CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
     
    SET IDENTITY_INSERT [dbo].[Table2] ON
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (2, N'A', N'10,40', N'Mtr')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (3, N'B', N'5', N'Kg')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (4, N'C', N'20', N'Ltr')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (5, N'D', N'30', N'Kg')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (6, N'D', N'10', N'Kg')
    SET IDENTITY_INSERT [dbo].[Table2] OFF
    /****** Object:  Table [dbo].[Table1]    Script Date: 01/08/2018 14:45:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table1](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](250) NULL,
    	[Qty] [float] NULL,
    	[Descriptions] [varchar](50) NULL,
     CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
     
    SET IDENTITY_INSERT [dbo].[Table1] ON
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (8, N'A', 10, N'Welcome')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (9, N'B', 5, N'Test')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (10, N'A', 40, N'Hello')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (11, N'C', 20, N'Nice')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (12, N'D', 30, N'Good')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (13, N'D', 10, N'Awesome')
    SET IDENTITY_INSERT [dbo].[Table1] OFF
    
    
    ; with mycte as 
    (select  [ID], [Name], [Qtys], Unit  from [dbo].[Table2]
     cross apply(Values ( parsename(Replace(Qty,',','.'),1)),( parsename(Replace(Qty,',','.'),2)),( parsename(Replace(Qty,',','.'),3)),(parsename(Replace(Qty,',','.'),4)))  d(Qtys)
      WHERE [Name]='A' and Qtys is not null
      )
    
      ,mycte1 as 
    
      (
      Select  a.[ID], a.[Name], [Qtys], Unit,Descriptions
      From mycte m
      join   table1 a on a.Name=m.Name and a.Qty=try_cast(m.Qtys as int))
    
    
    
      ,mycte2 as (
      SELECT t1.Name,t1.Unit,
           Stuff(( SELECT ',' + Cast(t2.QTYs as varchar(5))
               FROM mycte1 t2
              WHERE t2.Name = t1.Name and  t2.Unit = t1.Unit  
              ORDER BY Qtys
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Qtys
    			,
    			 Stuff(( SELECT ',' + Descriptions
               FROM mycte1 t2
              WHERE t2.Name = t1.Name and  t2.Unit = t1.Unit  
              ORDER BY Qtys
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Descriptions
      FROM mycte1 t1  
     GROUP BY t1.Name,t1.Unit 
    
      )
    
    Select Name,	Unit,	Qtys,	Descriptions   from mycte2
      Union ALL
    select a.Name,b.Unit, try_Cast(a.Qty  as varchar(50)) ,a.Descriptions  from 
    (select [ID], [Name], [Qty], [Descriptions] from Table1 )a
    left outer join
    (select [ID], [Name], [Qty], [Unit] from Table2) b
    on a.Name=b.Name and a.Qty=try_cast(b.Qty as int)
    WHERE b.Unit is not null
    
    
    
    
    
    drop TABLE [dbo].[Table1], [dbo].[Table2]

    Monday, January 8, 2018 9:18 PM
  • User2033107836 posted

    Hello

    I'm Getting error im using sql server 2008 R2

    Msg 195, Level 15, State 10, Line 23
    'try_cast' is not a recognized built-in function name.
    Msg 195, Level 15, State 10, Line 47
    'try_Cast' is not a recognized built-in function name.
    Msg 102, Level 15, State 1, Line 48
    Incorrect syntax near 'a'.
    Msg 102, Level 15, State 1, Line 50
    Incorrect syntax near 'b'.

    Tuesday, January 9, 2018 9:49 AM
  • User77042963 posted
    /****** Object:  Table [dbo].[Table2]    Script Date: 01/08/2018 14:45:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table2](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NULL,
    	[Qty] [varchar](50) NULL,
    	[Unit] [varchar](50) NULL,
     CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
     
    SET IDENTITY_INSERT [dbo].[Table2] ON
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (2, N'A', N'10,40', N'Mtr')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (3, N'B', N'5', N'Kg')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (4, N'C', N'20', N'Ltr')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (5, N'D', N'30', N'Kg')
    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (6, N'D', N'10', N'Kg')
    SET IDENTITY_INSERT [dbo].[Table2] OFF
    /****** Object:  Table [dbo].[Table1]    Script Date: 01/08/2018 14:45:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table1](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](250) NULL,
    	[Qty] [float] NULL,
    	[Descriptions] [varchar](50) NULL,
     CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
     
    SET IDENTITY_INSERT [dbo].[Table1] ON
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (8, N'A', 10, N'Welcome')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (9, N'B', 5, N'Test')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (10, N'A', 40, N'Hello')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (11, N'C', 20, N'Nice')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (12, N'D', 30, N'Good')
    INSERT [dbo].[Table1] ([ID], [Name], [Qty], [Descriptions]) VALUES (13, N'D', 10, N'Awesome')
    SET IDENTITY_INSERT [dbo].[Table1] OFF
    
    
    ; with mycte as 
    (select  [ID], [Name], [Qtys], Unit  from [dbo].[Table2]
     cross apply(Values ( parsename(Replace(Qty,',','.'),1)),( parsename(Replace(Qty,',','.'),2)),( parsename(Replace(Qty,',','.'),3)),(parsename(Replace(Qty,',','.'),4)))  d(Qtys)
      WHERE [Name]='A' and Qtys is not null
      )
    
      ,mycte1 as 
    
      (
      Select  a.[ID], a.[Name], [Qtys], Unit,Descriptions
      From mycte m
      join   table1 a on a.Name=m.Name and Cast(a.Qty as varchar(50))= m.Qtys )
    
    
    
      ,mycte2 as (
      SELECT t1.Name,t1.Unit,
           Stuff(( SELECT ',' + Cast(t2.QTYs as varchar(5))
               FROM mycte1 t2
              WHERE t2.Name = t1.Name and  t2.Unit = t1.Unit  
              ORDER BY Qtys
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Qtys
    			,
    			 Stuff(( SELECT ',' + Descriptions
               FROM mycte1 t2
              WHERE t2.Name = t1.Name and  t2.Unit = t1.Unit  
              ORDER BY Qtys
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Descriptions
      FROM mycte1 t1  
     GROUP BY t1.Name,t1.Unit 
    
      )
    
    Select Name,	Unit,	Qtys,	Descriptions   from mycte2
      Union ALL
    select a.Name,b.Unit,  Cast(a.Qty  as varchar(50)) ,a.Descriptions  from 
    (select [ID], [Name], [Qty], [Descriptions] from Table1 )a
    left outer join
    (select [ID], [Name], [Qty], [Unit] from Table2) b
    on a.Name=b.Name and Cast(a.Qty as varchar(50))= b.Qty
    WHERE b.Unit is not null
    
    
    
    
    
    drop TABLE [dbo].[Table1], [dbo].[Table2]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 9, 2018 2:25 PM
  • User364663285 posted

    Hi,
    Problem is due to Table2, like

    [Qty] [varchar](50) NULL,
    
    

    Please adjust it to float data type!

    By also adjusting line like

    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (2, N'A', N'10,40', N'Mtr')

    to be

    INSERT [dbo].[Table2] ([ID], [Name], [Qty], [Unit]) VALUES (2, N'A', 10, N'Mtr')


    Thursday, January 11, 2018 1:31 AM