How do i combine multiple rows to single row in sql

Question

CREATE TABLE [dbo].[Table1](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[CreateDate] [datetime] NOT NULL,
[CreatedByUserID] [int] NOT NULL,
[Customer] varchar(50) NOT NULL,

 CONSTRAINT [PK_dbo.Table1] PRIMARY KEY CLUSTERED 
(
    [Table1ID] ASC
))
SET IDENTITY_INSERT [dbo].table1 on 

insert into table1 ([Table1ID] ,    [CreateDate] ,  [CreatedByUserID] , [Customer] )values(1,GETDATE(),1,'customer1'),(2,GETDATE(),1,'customer1'),(3,GETDATE(),1,'customer1'),(4,GETDATE(),1,'customer1')  

SET IDENTITY_INSERT [dbo].table1 off  

CREATE TABLE [dbo].[Table2](
[Table2ID]     [int] IDENTITY(1,1) NOT NULL,    
[Table1ID] [int] NOT NULL,  
[ActualData] [nvarchar](255) NULL,  
[BoolCol1] [bit] NOT NULL,  
[IntCol] [int] NULL,    
[BoolCol2] [bit] NULL,  
[BoolCol3] [bit] NOT NULL,  
[BoolCol4] [bit] NOT NULL,  
[BoolCol5] [bit] NOT NULL, 
CONSTRAINT [PK_dbo.Table2] PRIMARY KEY CLUSTERED (  [Table2ID] ASC))

ALTER TABLE [dbo].[Table2]  WITH CHECK ADD 
 CONSTRAINT [FK_dbo.Table2_dbo.Table1_Table1ID] FOREIGN KEY([Table1ID])
REFERENCES [dbo].[Table1] ([Table1ID])

SET IDENTITY_INSERT [dbo].table2 on

insert into table2 
([Table2ID] ,   [Table1ID] ,    [ActualData] ,  [BoolCol1] ,    [IntCol] ,  [BoolCol2] ,    [BoolCol3] ,    [BoolCol4] ,    [BoolCol5] )
values (1,1,'Value 1',1,10,0,1,1,1),(2,1,'Value 2',0,20,1,1,1,0),(3,1,'Value 3',0,30,1,1,1,1),(4,1,'Value 4',0,40,1,1,1,0),(5,1,'Value 5',0,50,1,1,1,1)

SET IDENTITY_INSERT [dbo].table2 off

similarly i have around 5 tables which need to join and get the data from all columns...can i get a result similar like the below one

Table1ID    CreatedDate CreatedByUserID,Customer1       Value1 BoolCol1 Value1 IntCol1  Value1 BoolCol2 Value1 BoolCol3 Value1 BoolCol4 Value1 BoolCol5 Value2 BoolCol1 Value2 IntCol1  Value2 BoolCol2 Value2 BoolCol3 Value2 BoolCol4 Value2 BoolCol5 Value3...   Value3 Int

1   01-01-2015' 1   customer1   1   10  0   1   1   1   0   20  1   1   1   0   0   30

Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Tab Alleman, Oct 29 '15 at 13:29
Or if you need multiple columns, then here is your duplicate: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Tab Alleman, Oct 29 '15 at 13:31
Tab Alleman : The link talks about showing the result of only one column. In my case i have many columns which need to be displayed after PIVOT — Cheppy, Oct 29 '15 at 14:02
I don't have your data, so I can't picture what you mean about the one column and other columns. Can you post an example of the results you are getting from the posted code, and the results you want to get instead? — Tab Alleman, Oct 30 '15 at 11:43
CREATE TABLE [dbo].[Table1]( [Table1ID] [int] IDENTITY(1,1) NOT NULL, [CreateDate] [datetime] NOT NULL, [CreatedByUserID] [int] NOT NULL, [Customer] varchar(50) NOT NULL, CONSTRAINT [PK_dbo.Table1] PRIMARY KEY CLUSTERED ( [Table1ID] ASC ))ET IDENTITY_INSERT [dbo].table1 on insert into table1 ([Table1ID] , [CreateDate] , [CreatedByUserID] , [Customer] ) values(1,GETDATE(),1,'customer1') ,(2,GETDATE(),1,'customer1') ,(3,GETDATE(),1,'customer1') ,(4,GETDATE(),1,'customer1') SET IDENTITY_INSERT [dbo].table1 off — Cheppy, Oct 30 '15 at 13:19
CREATE TABLE [dbo].[Table2]( [Table2ID] [int] IDENTITY(1,1) NOT NULL, [Table1ID] [int] NOT NULL, [ActualData] [nvarchar](255) NULL, [BoolCol1] [bit] NOT NULL, [IntCol] [int] NULL, [BoolCol2] [bit] NULL, [BoolCol3] [bit] NOT NULL, [BoolCol4] [bit] NOT NULL, [BoolCol5] [bit] NOT NULL, CONSTRAINT [PK_dbo.Table2] PRIMARY KEY CLUSTERED ( [Table2ID] ASC ) ) GOGO ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [FK_dbo.Table2_dbo.Table1_Table1ID] FOREIGN KEY([Table1ID]) REFERENCES [dbo].[Table1] ([Table1ID])SET IDENTITY_INSERT [dbo].table2 on — Cheppy, Oct 30 '15 at 13:21
insert into table2 ([Table2ID] , [Table1ID] , [ActualData] , [BoolCol1] , [IntCol] , [BoolCol2] , [BoolCol3] , [BoolCol4] , [BoolCol5] ) values (1,1,'Value 1',1,10,0,1,1,1) ,(2,1,'Value 2',0,20,1,1,1,0) ,(3,1,'Value 3',0,30,1,1,1,1) ,(4,1,'Value 4',0,40,1,1,1,0) ,(5,1,'Value 5',0,50,1,1,1,1) SET IDENTITY_INSERT [dbo].table2 off — Cheppy, Oct 30 '15 at 13:21
Table1ID CreatedDate CreatedByUserID,Customer1 Value1 BoolCol1 Value1 IntCol1 Value1 BoolCol2 Value1 BoolCol3 Value1 BoolCol4 Value1 BoolCol5 Value2 BoolCol1 Value2 IntCol1 Value2 BoolCol2 Value2 BoolCol3 Value2 BoolCol4 Value2 BoolCol5 Value3... 1 01-01-2015' 1 customer1 1 10 0 1 1 1 0 20 1 1 1 0 0 — Cheppy, Oct 30 '15 at 13:21
first 3 comments has my table script and isert script. ..The last comment is the requeired output.....Similarly i ahve 5 tables to join to get the data and i need data from all these tables.Sorry , i am not able to format the text and it was not accepting many characters. thats why i have many comments..Apologies..... — Cheppy, Oct 30 '15 at 13:22

Guy Segev · Answer 1 · 2015-10-29T12:58:19.770

0

I guess your only choice is dynamic sql but my recommendation is having a multi rows table and leaving the rest for your server code (AKA ruby,python etc.).

edited Oct 29 '15 at 12:58

answered Oct 29 '15 at 11:01

Guy Segev

1,757
16
24

Thanks for answering..But this will give me multiple record set..I am expecting the result in a single row for a test1id – Cheppy Oct 29 '15 at 11:03
what do you mean? if you want to append values in a single column you can do `value1 + value2` for strings – Guy Segev Oct 29 '15 at 11:11
or `SELECT CONCAT(col1, col2, 'just text') AS new_col FROM table` – Guy Segev Oct 29 '15 at 11:13
(Imagine Test2 table has values Test2ID Test1ID Sectionname FileValidation PostEvent Combined 1 1 Sec1 1 5 1 2 1 Sec2 0 1 1 3 1 Sec3 1 2 1 – Cheppy Oct 29 '15 at 11:14
Test1ID CustomerName Sec1FileValidation Sec1PostEvent Sec1Combined Sec2FIleValidation Sec2PostEvent Sec2Combined Sec3Post Sec2Combined Sec3FileValidation 1 Cust1 1 5 1 1 1 2 1 1 this is what i am expecting – Cheppy Oct 29 '15 at 11:14
Combined output in a single row. This is for reporting purpose. – Cheppy Oct 29 '15 at 11:21
Ok. Got you. Is it a limited number of values? Or it's unlimited and the number of columns will be unlimited? – Guy Segev Oct 29 '15 at 11:33
Unlimited....The number of columns is depend on the number of sectionName .(Not statis)And the risk is i need data from all these columns..I think i need something dynamic – Cheppy Oct 29 '15 at 11:35
Thanks Guy, Does it mean that it is not possible through sql – Cheppy Oct 30 '15 at 00:04
I don't see a straight forward method for dynamic `join`, and in my opinion, if it's not easy, you probably shouldn't do it or do it differently. this is why i recommend it to be on the server code and not in the SQL query. – Guy Segev Oct 30 '15 at 10:33

How do i combine multiple rows to single row in sql

1 Answers1