Sql-server – Combining multiple-row data into a single row

sql-server-2008t-sql

I have a table that has columns like following

ID  Number  Value1  User1  Value2  User2  Value3  User3
--  ------  ------  -----  ------  -----  ------  ------
1   123     0       Jack
1   123                    1       TOM
1   123                                   2       Tim
1   456     0       Jones
1   456                    1       Jim
1   456                                   2       Carter

I need result like the following,

ID  Number  Value1  User1  Value2  User2  Value3  User3
--  ------  ------  -----  ------  -----  ------  ------
1   123     0       Jack   1       TOM    2       Tim
1   456     0       Jones  1       Jim    2       Carter

Can anyone please help me out on this?

The column ID remains same. The columns Value and User will go till Value6 and User6 and each row has values like one below the other. I need to combine all of them and show it as a single row.

Best Answer

Simple Group by will work for your expected output

select 
columnid,
Number,
max(Value1) as value1, max(User1)as user1 , max(Value2) as value2,  max(User2) as user2,max(Value3) as value3,max(User3) as user3
from 
table
group by
columnid,
number

Related Solutions

Sql-server – Combining data from multiple rows

Is it possible to redesign your schema? It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets.

CREATE TABLE dbo.Hardware -- hw?
(
    [Event_Num] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(100) NOT NULL,
    [Install_Date] DATE NULL,  -- Install after pulling?
    [Pull_Date] DATE NOT NULL,
    [Install_Tech] INT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    [Pull_Tech] DATE NOT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    <Add other common fields here>
)

CREATE TABLE dbo.ComponentType -- Or some better name for PMP, Motor, dis, etc. table
(
    [ComponentTypeId] INT NOT NULL PRIMARY KEY,
    [Description] NVARCHAR(50) NOT NULL -- This is where you would put PMP, Motor, etc.
)

CREATE TABLE dbo.WorkPerformed
(
    [WorkPerformedId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [ComponentTypeId] INT NOT NULL FOREIGN KEY REFERENCES dbo.ComponentType(ComponentTypeId), -- FK to type lets us reuse this structure for all components and prevents the need to pivot the excel data
    [Event_Num] INT NOT NULL FOREIGN KEY REFERENCES dbo.Hardware(Event_Num), -- Allows you to associate the work performed with the hw
    [Description] NVARCHAR(400) NULL,
    [SerialNumber] NVARCHAR(50) NOT NULL,
    [Part] NVARCHAR(50) NOT NULL,
    <Other valuable info not currently tracking, ie pulled/installed, tech doing work, etc>
)

Obviously that is just a rough sample of the way the schema could be setup. I'm sure you can see that mapping the data will be much easier now, and more flexable for future updates. In order to maintain backwards compatibility, if needed, you could just create a view with the current table's name and select the data from the new tables.

If going down this path is not possible/acceptable I would look at pulling your data out of the temp table and inserting it into your current table with a PIVOT. See this TechNet article for basic information about pivoting (the syntax from 2008R2 will work in 2012).

SQL Server – Crosstab Pivot or Not?

This has nothing to do with pivoting, you simply want to replicate your three columns conditionally three times, according to the number of groups. A series of CASE expressions would do the job perfectly:

SELECT
  InvoiceRef,
  Group1Date   = CASE Group1 WHEN 'Y' THEN Date   END,
  Group1Value1 = CASE Group1 WHEN 'Y' THEN Value1 END,
  Group1Value2 = CASE Group1 WHEN 'Y' THEN Value2 END,
  Group2Date   = CASE Group2 WHEN 'Y' THEN Date   END,
  Group2Value1 = CASE Group2 WHEN 'Y' THEN Value1 END,
  Group2Value2 = CASE Group2 WHEN 'Y' THEN Value2 END,
  Group3Date   = CASE Group3 WHEN 'Y' THEN Date   END,
  Group3Value1 = CASE Group3 WHEN 'Y' THEN Value1 END,
  Group3Value2 = CASE Group3 WHEN 'Y' THEN Value2 END
FROM
  dbo.atable
;

Best Answer

Related Solutions

Sql-server – Combining data from multiple rows

SQL Server – Crosstab Pivot or Not?

Related Question