April 25, 2009 at 9:01 am
Hi
I have got two tables named "Config" and "Config Values".
The fields in the "Config" are ConfigId(Primary Key) and TypeName.
The fields for the "Config Values" are Id(Primary Key), ConfigID(Foreign Key from Config table) and Values.
The data in Config table
Id Type
1 Mechanical
2 Electronics
3 Electrical
The values in the "Config Values" are
Id | ConfigId | Values
----------------------
1 | 1 | 10
2 | 2 | 20
3 | 3 | 30
4 | 1 | 40
5 | 2 | 50
6 | 3 | 60
Using a query, I want the resultant set as
Mechanical | Electronics | Electrical
------------------------------------------------
10 | 20 | 30
40 | 50 | 60
Can somebody help please
April 25, 2009 at 9:07 am
http://www.mssqltips.com/tip.asp?tip=1019
I never used it though but I Don't know if your are looking for the same thing.
April 26, 2009 at 9:47 am
Hi tinku
Thanks for the reply. Pivot clause cannot be used in this scenario, because, the column name is not static, it is taken from the first table "Config" 🙁
April 26, 2009 at 9:55 am
Hi
What about dynamic SQL to create the PIVOT statement?
Greets
Flo
April 26, 2009 at 4:43 pm
Hi,
could you please describe a little more the purpose of your requirement together with some code you've tried so far?
What kind of project are you woring on?
How can be determined, that 10,20, and 30 belong together and not 10, 50, and 30?
April 27, 2009 at 10:55 am
Hi
The data are grouped according to the value of the config table.
The fields in the config table are ID, Type. The data in the config table are
1 -- Mechanical
2 -- Electronics
3 -- Electrical
The fields in the "Config values" are Id, ConfigId (Foreign key of Config table), values
The data in the "Config values" are
Id -- ConfigId -- Values
1 -- 1 --------- --10
2 --- 2----------- 20 One set of data
3 --- 3 ----------- 30
------------------------------------------------
4 ----1------------40
5-----2------------50 Second set of data
6 ----3------------ 60
I want the resultant set as
All values from the "Config values" for a configid as one column and the column name from the table "Config".
For eg: for the Config Id "1" in the Config values the data should be
Mechanical (Column Name)
10
40
so the resultant as
Mechanical | Electronics| Electrical
10 | 20 |30
40 | 50 |60
April 27, 2009 at 12:30 pm
Hi,
I need to ask the same questions as in my previous post again since you didn't answer one of them yet.
You're just rephrasing the original question.
When I asked how the data sets that belong together can be recognized I didn't mean a visual method (like you showed) but more a programmable rule.
The requirement itself is -aside of the above- pretty clear.
My question regarding the purpose goes a little more in the direction whether this is a real-world problem or a homework. Again, not answered yet.
Also you've missed to show us what you've tried so far.
So please help us to help you.
February 17, 2010 at 2:32 am
Hi.
I am sorry, but I do not have time to have a complex discussion but the following code (see the end of this blog) should help.
The table KPISelect holds all of our sales data for the previous month. The month is held as MMYY for historic reasons.
The table KPI has one column for each month. There are about 30 rows for each member of staff. Each one is a different KPI type.
The critical bit from your requirement is shown in the code that produces the SQL to update the latest monthly column from the month number in the thousands of records in the KPISelect table.
Enjoy.
Roy
USE [POR-MainDB]
GO
/****** Object: StoredProcedure [dbo].[uspKPI2UpdateKPITable] Script Date: 02/17/2010 10:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Roy Turner
-- Create date: 20/01/2009
-- Description:Populates tbl_KPI with values stored in tbl_KPISelect
-- =============================================
ALTER PROCEDURE [dbo].[uspKPI2UpdateKPITable]
-- Add the parameters for the stored procedure here
@Month as nchar(4),
@NextMonth as nchar(4),
@Result as int out
AS
BEGIN
Declare @Check as nchar(2)
Declare @MonthNo as nchar(2)
Declare @YearNo as nchar(2)
Declare @YearNum as nchar(4)
Declare @NextMonthNo as nchar(2)
Declare @NextYearNo as nchar(2)
Declare @NextYearNum as nchar(4)
declare @sqlString as nvarchar(max)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set @Result = 0
Set @Check = (select top 1 MonthNum from tbl_KPISelect
where MonthNum = left(@Month,2))
If @@Error != 0
begin
set @Result = -201
Return @Result
end
If @Check is null
begin
set @Result = -202
Return @Result
end
BEGIN TRAN KPI2
set @MonthNo = left(@Month, 2)
set @sqlString =
CASE @MonthNo
when '01' then 'JanValue'
when '02' then 'FebValue'
when '03' then 'MarValue'
when '04' then 'AprValue'
when '05' then 'MayValue'
when '06' then 'JunValue'
when '07' then 'JulValue'
when '08' then 'AugValue'
when '09' then 'SepValue'
when '10' then 'OctValue'
when '11' then 'NovValue'
when '12' then 'DecValue'
end
set @YearNo = right(@Month, 2)
set @YearNum = '20' + @YearNo
set @sqlString = 'update tbl_KPI set ' + @sqlstring +
' = (select Value from tbl_KPISelect ' +
'where YearNum = tbl_KPI.YearNum and ' +
'MonthNum = ' + @MonthNo + ' and ' +
'Code = tbl_KPI.Code and ' +
'Type = tbl_KPI.Type and ' +
'RowNum = tbl_KPI.RowNum) ' +
'where tbl_KPI.rowNum <> 60 and YearNum = ' + @YearNum
--print @sqlstring
exec (@sqlstring)
If @@Error != 0
begin
set @Result = -203
goto KPI2Failed
end
-- Now process C/F into following month
set @NextMonthNo = left(@NextMonth, 2)
set @NextYearNo = right(@NextMonth, 2)
set @NextYearNum = '20' + @NextYearNo
set @sqlString =
CASE @NextMonthNo
when '01' then 'JanValue'
when '02' then 'FebValue'
when '03' then 'MarValue'
when '04' then 'AprValue'
when '05' then 'MayValue'
when '06' then 'JunValue'
when '07' then 'JulValue'
when '08' then 'AugValue'
when '09' then 'SepValue'
when '10' then 'OctValue'
when '11' then 'NovValue'
when '12' then 'DecValue'
end
set @sqlString = 'update tbl_KPI set ' + @sqlstring +
' = (select Value from tbl_KPISelect ' +
'where YearNum = tbl_KPI.YearNum and ' +
'MonthNum = ' + @NextMonthNo + ' and ' +
'Code = tbl_KPI.Code and ' +
'Type = tbl_KPI.Type and ' +
'RowNum = tbl_KPI.RowNum) ' +
'where tbl_KPI.YearNum = ' + @NextYearNum + ' and ' +
'tbl_KPI.RowNum = 60'
--print @sqlstring
exec (@sqlstring)
If @@Error != 0
begin
set @Result = -204
goto KPI2Failed
end
Commit TRAN KPI2
goto KPI2Exit
KPI2Failed:
Rollback TRAN KPI2
KPI2Exit:
Return @Result
END
February 17, 2010 at 2:32 am
Hi.
I am sorry, but I do not have time to have a complex discussion but the following code (see the end of this blog) should help.
The table KPISelect holds all of our sales data for the previous month. The month is held as MMYY for historic reasons.
The table KPI has one column for each month. There are about 30 rows for each member of staff. Each one is a different KPI type.
The critical bit from your requirement is shown in the code that produces the SQL to update the latest monthly column from the month number in the thousands of records in the KPISelect table.
Enjoy.
Roy
USE [POR-MainDB]
GO
/****** Object: StoredProcedure [dbo].[uspKPI2UpdateKPITable] Script Date: 02/17/2010 10:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Roy Turner
-- Create date: 20/01/2009
-- Description:Populates tbl_KPI with values stored in tbl_KPISelect
-- =============================================
ALTER PROCEDURE [dbo].[uspKPI2UpdateKPITable]
-- Add the parameters for the stored procedure here
@Month as nchar(4),
@NextMonth as nchar(4),
@Result as int out
AS
BEGIN
Declare @Check as nchar(2)
Declare @MonthNo as nchar(2)
Declare @YearNo as nchar(2)
Declare @YearNum as nchar(4)
Declare @NextMonthNo as nchar(2)
Declare @NextYearNo as nchar(2)
Declare @NextYearNum as nchar(4)
declare @sqlString as nvarchar(max)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set @Result = 0
Set @Check = (select top 1 MonthNum from tbl_KPISelect
where MonthNum = left(@Month,2))
If @@Error != 0
begin
set @Result = -201
Return @Result
end
If @Check is null
begin
set @Result = -202
Return @Result
end
BEGIN TRAN KPI2
set @MonthNo = left(@Month, 2)
set @sqlString =
CASE @MonthNo
when '01' then 'JanValue'
when '02' then 'FebValue'
when '03' then 'MarValue'
when '04' then 'AprValue'
when '05' then 'MayValue'
when '06' then 'JunValue'
when '07' then 'JulValue'
when '08' then 'AugValue'
when '09' then 'SepValue'
when '10' then 'OctValue'
when '11' then 'NovValue'
when '12' then 'DecValue'
end
set @YearNo = right(@Month, 2)
set @YearNum = '20' + @YearNo
set @sqlString = 'update tbl_KPI set ' + @sqlstring +
' = (select Value from tbl_KPISelect ' +
'where YearNum = tbl_KPI.YearNum and ' +
'MonthNum = ' + @MonthNo + ' and ' +
'Code = tbl_KPI.Code and ' +
'Type = tbl_KPI.Type and ' +
'RowNum = tbl_KPI.RowNum) ' +
'where tbl_KPI.rowNum <> 60 and YearNum = ' + @YearNum
--print @sqlstring
exec (@sqlstring)
If @@Error != 0
begin
set @Result = -203
goto KPI2Failed
end
-- Now process C/F into following month
set @NextMonthNo = left(@NextMonth, 2)
set @NextYearNo = right(@NextMonth, 2)
set @NextYearNum = '20' + @NextYearNo
set @sqlString =
CASE @NextMonthNo
when '01' then 'JanValue'
when '02' then 'FebValue'
when '03' then 'MarValue'
when '04' then 'AprValue'
when '05' then 'MayValue'
when '06' then 'JunValue'
when '07' then 'JulValue'
when '08' then 'AugValue'
when '09' then 'SepValue'
when '10' then 'OctValue'
when '11' then 'NovValue'
when '12' then 'DecValue'
end
set @sqlString = 'update tbl_KPI set ' + @sqlstring +
' = (select Value from tbl_KPISelect ' +
'where YearNum = tbl_KPI.YearNum and ' +
'MonthNum = ' + @NextMonthNo + ' and ' +
'Code = tbl_KPI.Code and ' +
'Type = tbl_KPI.Type and ' +
'RowNum = tbl_KPI.RowNum) ' +
'where tbl_KPI.YearNum = ' + @NextYearNum + ' and ' +
'tbl_KPI.RowNum = 60'
--print @sqlstring
exec (@sqlstring)
If @@Error != 0
begin
set @Result = -204
goto KPI2Failed
end
Commit TRAN KPI2
goto KPI2Exit
KPI2Failed:
Rollback TRAN KPI2
KPI2Exit:
Return @Result
END
February 17, 2010 at 2:32 am
Hi.
I am sorry, but I do not have time to have a complex discussion but the following code (see the end of this blog) should help.
The table KPISelect holds all of our sales data for the previous month. The month is held as MMYY for historic reasons.
The table KPI has one column for each month. There are about 30 rows for each member of staff. Each one is a different KPI type.
The critical bit from your requirement is shown in the code that produces the SQL to update the latest monthly column from the month number in the thousands of records in the KPISelect table.
Enjoy.
Roy
USE [POR-MainDB]
GO
/****** Object: StoredProcedure [dbo].[uspKPI2UpdateKPITable] Script Date: 02/17/2010 10:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Roy Turner
-- Create date: 20/01/2009
-- Description:Populates tbl_KPI with values stored in tbl_KPISelect
-- =============================================
ALTER PROCEDURE [dbo].[uspKPI2UpdateKPITable]
-- Add the parameters for the stored procedure here
@Month as nchar(4),
@NextMonth as nchar(4),
@Result as int out
AS
BEGIN
Declare @Check as nchar(2)
Declare @MonthNo as nchar(2)
Declare @YearNo as nchar(2)
Declare @YearNum as nchar(4)
Declare @NextMonthNo as nchar(2)
Declare @NextYearNo as nchar(2)
Declare @NextYearNum as nchar(4)
declare @sqlString as nvarchar(max)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set @Result = 0
Set @Check = (select top 1 MonthNum from tbl_KPISelect
where MonthNum = left(@Month,2))
If @@Error != 0
begin
set @Result = -201
Return @Result
end
If @Check is null
begin
set @Result = -202
Return @Result
end
BEGIN TRAN KPI2
set @MonthNo = left(@Month, 2)
set @sqlString =
CASE @MonthNo
when '01' then 'JanValue'
when '02' then 'FebValue'
when '03' then 'MarValue'
when '04' then 'AprValue'
when '05' then 'MayValue'
when '06' then 'JunValue'
when '07' then 'JulValue'
when '08' then 'AugValue'
when '09' then 'SepValue'
when '10' then 'OctValue'
when '11' then 'NovValue'
when '12' then 'DecValue'
end
set @YearNo = right(@Month, 2)
set @YearNum = '20' + @YearNo
set @sqlString = 'update tbl_KPI set ' + @sqlstring +
' = (select Value from tbl_KPISelect ' +
'where YearNum = tbl_KPI.YearNum and ' +
'MonthNum = ' + @MonthNo + ' and ' +
'Code = tbl_KPI.Code and ' +
'Type = tbl_KPI.Type and ' +
'RowNum = tbl_KPI.RowNum) ' +
'where tbl_KPI.rowNum <> 60 and YearNum = ' + @YearNum
--print @sqlstring
exec (@sqlstring)
If @@Error != 0
begin
set @Result = -203
goto KPI2Failed
end
-- Now process C/F into following month
set @NextMonthNo = left(@NextMonth, 2)
set @NextYearNo = right(@NextMonth, 2)
set @NextYearNum = '20' + @NextYearNo
set @sqlString =
CASE @NextMonthNo
when '01' then 'JanValue'
when '02' then 'FebValue'
when '03' then 'MarValue'
when '04' then 'AprValue'
when '05' then 'MayValue'
when '06' then 'JunValue'
when '07' then 'JulValue'
when '08' then 'AugValue'
when '09' then 'SepValue'
when '10' then 'OctValue'
when '11' then 'NovValue'
when '12' then 'DecValue'
end
set @sqlString = 'update tbl_KPI set ' + @sqlstring +
' = (select Value from tbl_KPISelect ' +
'where YearNum = tbl_KPI.YearNum and ' +
'MonthNum = ' + @NextMonthNo + ' and ' +
'Code = tbl_KPI.Code and ' +
'Type = tbl_KPI.Type and ' +
'RowNum = tbl_KPI.RowNum) ' +
'where tbl_KPI.YearNum = ' + @NextYearNum + ' and ' +
'tbl_KPI.RowNum = 60'
--print @sqlstring
exec (@sqlstring)
If @@Error != 0
begin
set @Result = -204
goto KPI2Failed
end
Commit TRAN KPI2
goto KPI2Exit
KPI2Failed:
Rollback TRAN KPI2
KPI2Exit:
Return @Result
END
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply