locked
Resultset In One Column RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my table data

    USE [master]
    GO
    /****** Object:  Table [dbo].[Product]    Script Date: 09/10/2019 00:49:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Product](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Article_Code] [varchar](50) NULL,
    	[color] [varchar](50) NULL,
    	[Size] [varchar](50) NULL,
    	[Stock_Qty] [float] NULL,
     CONSTRAINT [PK_Product] 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].[Product] ON
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (1, N'A1', N'Blue', N'7', 2)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (2, N'A1', N'Blue', N'8', 1)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (3, N'A1', N'Blue', N'9', 0)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (4, N'B1', N'Red', N'7', 4)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (5, N'B1', N'Red', N'10', 5)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (6, N'C1', N'Green', N'7', 1)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (7, N'B1', N'Red', N'8', 2)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (8, N'C1', N'Green', N'10', 2)
    SET IDENTITY_INSERT [dbo].[Product] OFF
    

    I want output in one column like this below

    Result set In One Column

    Code : A1 – Color : Blue

    7/2  – 8/1

    Code : B1 – Color : Red

    7/4  – 8/2 – 10/5

    Code : C1 – Color : Green

    7/1  – 10/2

    How to do so 

    Monday, September 9, 2019 7:21 PM

Answers

  • User-719153870 posted

    Hi Gopi.MCA,

    I want output in one column like this below

    Result set In One Column

    Code : A1 – Color : Blue

    7/2  – 8/1

    Code : B1 – Color : Red

    7/4  – 8/2 – 10/5

    Code : C1 – Color : Green

    7/1  – 10/2

    How to do so 

    This output is in one column and three rows?

    If so, you need to know how to add newline character in SQL, please refer to How to insert a line break in a SQL Server VARCHAR/NVARCHAR string , you will need a CHAR(13) as a newline character in your query string and change a little setting in your SSMS.

    Please refer to below code:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Product](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Article_Code] [varchar](50) NULL,
    	[color] [varchar](50) NULL,
    	[Size] [varchar](50) NULL,
    	[Stock_Qty] [float] NULL,
     CONSTRAINT [PK_Product] 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].[Product] ON
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (1, N'A1', N'Blue', N'7', 2)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (2, N'A1', N'Blue', N'8', 1)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (3, N'A1', N'Blue', N'9', 0)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (4, N'B1', N'Red', N'7', 4)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (5, N'B1', N'Red', N'10', 5)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (6, N'C1', N'Green', N'7', 1)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (7, N'B1', N'Red', N'8', 2)
    INSERT [dbo].[Product] ([ID], [Article_Code], [color], [Size], [Stock_Qty]) VALUES (8, N'C1', N'Green', N'10', 2)
    SET IDENTITY_INSERT [dbo].[Product] OFF
    
    --select * from Product
    
    create table #A
    (
    [Result set In One Column] varchar(100)
    )
    
    create table #ss
    (
    [Size] [varchar](50) NULL,
    [Stock_Qty] [float] NULL,
    )
    go
    
    declare @i int
    declare @j int
    declare @c varchar(50)
    declare @a varchar(50)
    declare @s varchar(2000)
    declare @sa varchar(2000)
    set @i=1
    while @i<=3
    begin
    set @j=CONVERT(varchar(50),(@i-1))
    set @c=(select top 1 a.Article_Code from (select distinct(Article_Code) from Product)a where a.Article_Code not in (select top (@i-1) a.Article_Code from (select distinct(Article_Code) from Product)a))
    drop table #ss;
    select b.Size,b.Stock_Qty into #ss from( select Size,Stock_Qty from Product where Article_Code=(select top 1 a.Article_Code from (select distinct(Article_Code) from Product)a where a.Article_Code not in (select top (@i-1) a.Article_Code from (select distinct(Article_Code) from Product)a)))b
    set @j=1
    set @sa=''
    while @j<=(select COUNT([Size]) from #ss)
    begin
    set @a=(select top 1 Size from #ss where Size not in (select top (@j-1) Size from #ss))
    set @sa+=@a+'/'+(select convert(varchar(50),Stock_Qty) from #ss where Size=@a)+'- '
    set @j+=1
    end
    set @sa=SUBSTRING(@sa,1,LEN(@sa)-1)
    insert into #A values( 'Code:'+@c+'-Color:'+(select distinct(color) from Product where Article_Code=(@c))+ CHAR(13) + @sa)
    set @i+=1
    end
    select * from #A
    drop table #ss
    drop table #A
    

    The highlighted part should be what you want, and this will get you below result:

    Notice: As you can see, there's no newline in my dataset, that's because my SSMS is too old and can't change the setting mentioned above link.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 10, 2019 5:40 AM