May 17, 2010 at 4:14 am
Friends,
I have a SQL table in which I have data in 35 columns wherein I from 24 columns which repeats 12 months twice like FJAN, FFEB, FMAR... till FDEC and then AJAN, AFEB, AMAR.... till ADEC. In the first 11 columns the data has respective descriptions for these months. What I need is to transpose data for all the 35 columns into rows where I need new columns like Months & Values. In months column I need to shows FJAN, FFEB, FMAR and so on and then numeric value in Values Columns. Similarly I need another Months & Values column where I need to show AJAN, AFEB, AMAR and in values column values for that month. Please suggest a SQl query to transpose this data.
I have attached an excel file in which there are 2 sheets showing the data state now and what is required....
Please help me out friends....
Thanks in Advance...
May 17, 2010 at 6:23 am
Something like this perhaps:
create table #t (
id int identity(1,1),
AJAN int,
AFEB int,
AMAR int,
FJAN int,
FFEB int,
FMAR int
)
insert into #t (AJAN,AFEB,AMAR,FJAN,FFEB,FMAR) values (1,2,3,4,5,6)
insert into #t (AJAN,AFEB,AMAR,FJAN,FFEB,FMAR) values (11,12,13,14,15,16)
select * from #t
select
id,
'A'+c as amonth,
sum(case when c1='A' then val else 0 end) as aval,
'F'+c as fmonth,
sum(case when c1='F' then val else 0 end) as fval
from (
select id, val, substring(col,1,1) as c1, substring(col,2,3) as c
from #t
unpivot (val for col in (AJAN,AFEB,AMAR,FJAN,FFEB,FMAR)) up
) t
group by id,c
order by id,c
I have only used a single id column and only JAN-MAR but it should be simple to extend to the full table definition. 🙂
/SG
May 17, 2010 at 10:31 pm
Hi Stefan,
Thanks for all your support.
I have a request here as I'm a beginner to SQL so can you please take column (fields) from my excel attachment and prepare a code for me to transpose the whole table using the dummy data I have provided in the attachment.
It would be great help.
Thank you very much.
Deepak Sharma
May 17, 2010 at 11:10 pm
I have tried replicating the code below but that didn't worked 🙁
select
2_Org_Code, 2_Org_Name, 3_Org_Code, 3_Org_Name, YEAR, Serial No, Serial Name, Serial_Sub_No, Serial_Sub_Name, NC, NC_Description,
'A'+c as amonth,
sum(case when c1='A' then val else 0 end) as aval,
'F'+c as fmonth,
sum(case when c1='F' then val else 0 end) as fval
from (
select 2_Org_Code, 2_Org_Name, 3_Org_Code, 3_Org_Name, YEAR, Serial No, Serial Name, Serial_Sub_No, Serial_Sub_Name, NC, NC_Description, val, substring(col,1,1) as c1, substring(col,2,3) as c
from dbo.Hours_Actual
unpivot (val for col in (FJAN,FFEB,FMAR,FAPR,FMAY,FJUN,FJUL,FAUG,FSEP,FOCT,FNOV,FDEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG,ASEP,AOCT,ANOV,ADEC)) up
) dbo.Hours_Actual
group by JOB,c
order by JOB,c
***** In the above code I have renamed all the field to the best of my knowledge and table name from "t" to dbo.Hours_Actual which is the name of my table
Please help me out...
Thanks
Deepak Sharma
May 17, 2010 at 11:27 pm
Deepak, the excel was a good one.. but we need DDLs to work on man.. creating 35 column table, some 100 rows and then working on the request will take a a lot of time.. so please provide us,
1. Table Structures - CREATE TABLE scripts
2. Constraints or Indexes in the table, - CREATE SCRIPTS
3. SAMPLE DATA - INSERT INTO TABLE scripts
4. Desired output - some visual representation of this.
Please have a look at how u should present the question to the best help:
May 18, 2010 at 12:06 am
Dear Friend,
Since I'm new to SQL so I have used Import/Export Data utility to import table structure and data in a database. I don't know how to create scripts for creating table or insertion of records. I can do basics, however, in this case I have used the Import/Export Data utility of SQL Server. As far as sample visual is concerned I think you can get the best idea from the excel file.
I hope you can understand. Please help...
Thanks
Deepak Sharma
May 18, 2010 at 1:09 am
Deepak, let me tell u this, this was one of the longest ask i had worked on :-D, not cos' of the code, but for importing data into my machine!
Here is the code for your requirement! Please replace the table name [Now] with your orginal table name.
;WITH F_Month_CTe AS
(
SELECT
[2_Org_Code] ,
[2_Org_Name] ,
[3_Org_Code] ,
[3_Org_Name] ,
[YEAR] ,
[Serial No] ,
[Serial_Name] ,
[Serial_Sub_No] ,
[Serial_Sub_Name] ,
[NC] ,
[NC_Description] ,
[VALUE],
[COLS]
FROM
(
SELECT * FROM [NOW] -- Replace with your table name
)UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
FJAN,FFEB,FMAR,FAPR,FMAY,FJUN,
FJUL,FAUG,FSEP,FOCT,FNOV,FDEC
)
)UNPIVOT_HANDLE
),
A_Month_CTe AS
(
SELECT
[2_Org_Code] ,
[2_Org_Name] ,
[3_Org_Code] ,
[3_Org_Name] ,
[YEAR] ,
[Serial No] ,
[Serial_Name] ,
[Serial_Sub_No] ,
[Serial_Sub_Name] ,
[NC] ,
[NC_Description] ,
[VALUE],
[COLS]
FROM
(
SELECT * FROM [NOW] -- Replace with your table name
)UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,
AJUL,AAUG,ASEP,AOCT,ANOV,ADEC
)
)UNPIVOT_HANDLE
)
SELECT
T1.[2_Org_Code] ,
T1.[2_Org_Name] ,
T1.[3_Org_Code] ,
T1.[3_Org_Name] ,
T1.[YEAR] ,
T1.[Serial No] ,
T1.[Serial_Name] ,
T1.[Serial_Sub_No] ,
T1.[Serial_Sub_Name] ,
T1.[NC] ,
T1.[NC_Description] ,
T1.[COLS] AS F_Months,
T1.[VALUE] AS F_Values,
T2.[COLS] AS A_Months,
T2.[VALUE] AS A_Values
FROM F_Month_CTe T1
INNER JOIN A_Month_CTe T2
ON T1.[2_Org_Code] = T2.[2_Org_Code] AND
T1.[2_Org_Name] = T2.[2_Org_Name] AND
T1.[3_Org_Code] = T2.[3_Org_Code] AND
T1.[3_Org_Name] = T2.[3_Org_Name] AND
T1.[YEAR] = T2.[YEAR] AND
T1.[Serial No] = T2.[Serial No] AND
T1.[Serial_Name] = T2.[Serial_Name] AND
T1.[Serial_Sub_No] = T2.[Serial_Sub_No] AND
T1.[Serial_Sub_Name] = T2.[Serial_Sub_Name] AND
T1.[NC] = T2.[NC] AND
T1.[NC_Description] = T2.[NC_Description] AND
SUBSTRING(T1.[COLS] , 2, 3) = SUBSTRING(T2.[COLS] , 2, 3)
Please tell us if this worked for you! If not, give us more clear requirement and data
Cheers! 😎
May 18, 2010 at 2:42 am
sharmadipak (5/17/2010)
I have tried replicating the code below but that didn't worked 🙁
I hope this will work:
select
[2_Org_Code], [2_Org_Name], [3_Org_Code], [3_Org_Name], [YEAR], [Serial No], [Serial_Name], [Serial_Sub_No], [Serial_Sub_Name], [NC], [NC_Description],
'A'+c as amonth,
sum(case when c1='A' then val else 0 end) as aval,
'F'+c as fmonth,
sum(case when c1='F' then val else 0 end) as fval
from (
select
[2_Org_Code], [2_Org_Name], [3_Org_Code], [3_Org_Name], [YEAR], [Serial No], [Serial_Name], [Serial_Sub_No], [Serial_Sub_Name], [NC], [NC_Description],
val, substring(col,1,1) as c1, substring(col,2,3) as c
from dbo.Hours_Actual -- << Your table name here
unpivot (val for col in (FJAN,FFEB,FMAR,FAPR,FMAY,FJUN,FJUL,FAUG,FSEP,FOCT,FNOV,FDEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG,ASEP,AOCT,ANOV,ADEC)) up
) t
group by [2_Org_Code], [2_Org_Name], [3_Org_Code], [3_Org_Name], [YEAR], [Serial No], [Serial_Name], [Serial_Sub_No], [Serial_Sub_Name], [NC], [NC_Description], c
May 18, 2010 at 4:26 am
Dear thanks a lot for your efforts, I'm very thankful that you have spent so much time on my issue. Really appreciate that.
I have tried running the code, however, the query neither showing any data nor any error.
The columns are in the exact position like I required, however, It displays, query executed successfully with 0 rows affected.
I have tried the below code changed the table name to dbo.Cost_Actual with square brackets and without 🙁
;WITH F_Month_CTe AS
(
SELECT
[2_Org_Code] ,
[2_Org_Name] ,
[3_Org_Code] ,
[3_Org_Name] ,
[YEAR] ,
[Serial No] ,
[Serial_Name] ,
[Serial_Sub_No] ,
[Serial_Sub_Name] ,
[NC] ,
[NC_Description] ,
[VALUE],
[COLS]
FROM
(
SELECT * FROM dbo.Cost_Actual -- Replace with your table name
) UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
FJAN,FFEB,FMAR,FAPR,FMAY,FJUN,
FJUL,FAUG,FSEP,FOCT,FNOV,FDEC
)
)UNPIVOT_HANDLE
),
A_Month_CTe AS
(
SELECT
[2_Org_Code] ,
[2_Org_Name] ,
[3_Org_Code] ,
[3_Org_Name] ,
[YEAR] ,
[Serial No] ,
[Serial_Name] ,
[Serial_Sub_No] ,
[Serial_Sub_Name] ,
[NC] ,
[NC_Description] ,
[VALUE],
[COLS]
FROM
(
SELECT * FROM dbo.Cost_Actual -- Replace with your table name
) UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,
AJUL,AAUG,ASEP,AOCT,ANOV,ADEC
)
)UNPIVOT_HANDLE
)
SELECT
T1.[2_Org_Code] ,
T1.[2_Org_Name] ,
T1.[3_Org_Code] ,
T1.[3_Org_Name] ,
T1.[YEAR] ,
T1.[Serial No] ,
T1.[Serial_Name] ,
T1.[Serial_Sub_No] ,
T1.[Serial_Sub_Name] ,
T1.[NC] ,
T1.[NC_Description] ,
T1.[COLS] AS F_Months,
T1.[VALUE] AS F_Values,
T2.[COLS] AS A_Months,
T2.[VALUE] AS A_Values
FROM F_Month_CTe T1
INNER JOIN A_Month_CTe T2
ON T1.[2_Org_Code] = T2.[2_Org_Code] AND
T1.[2_Org_Name] = T2.[2_Org_Name] AND
T1.[3_Org_Code] = T2.[3_Org_Code] AND
T1.[3_Org_Name] = T2.[3_Org_Name] AND
T1.[YEAR] = T2.[YEAR] AND
T1.[Serial No] = T2.[Serial No] AND
T1.[Serial_Name] = T2.[Serial_Name] AND
T1.[Serial_Sub_No] = T2.[Serial_Sub_No] AND
T1.[Serial_Sub_Name] = T2.[Serial_Sub_Name] AND
T1.[NC] = T2.[NC] AND
T1.[NC_Description] = T2.[NC_Description] AND
SUBSTRING(T1.[COLS] , 2, 3) = SUBSTRING(T2.[COLS] , 2, 3)
May 18, 2010 at 4:34 am
Did you try my query ?
It works for me. 😎
May 18, 2010 at 4:50 am
Deepak, are you sure, the code i supplied dint provide any results? Cos i imported the excel data that you provided, then created teh query on top of that.. it worked for me very well and the result matched the exact same in the "Required" worksheet of sample.xlsx.
Are u sure taht the data u provided in the sample.xlsx is same as the data in your database?
May 19, 2010 at 2:29 am
For Cold Coffee
Dear Friend,
Thanks a lot for your uninterrupted support.....
I have just changed the name of the some columns and data is now some 139 records, however, the number of columns and the sequence is the same. When I run on "Now" table it works fine, however, in case of my table it is not working 🙁
I'm attaching a sample excel sheet for your reference. I have used the below attached code which I modified as per the new requirement:
;WITH F_Month_CTe AS
(
SELECT
[L2_ORG_CODE] ,
[L2_ORG_NAME] ,
[L3_ORG_CODE] ,
[L3_ORG_NAME] ,
[YEAR] ,
[JOB] ,
[JOB_NAME] ,
[SUB_JOB] ,
[SUB_JOB_NAME] ,
[EOC_NC] ,
[EOC_NC_DESC] ,
[VALUE],
[COLS]
FROM
(
SELECT * FROM [Cost_Actual]
) UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
FCST_JAN,FCST_FEB,FCST_MAR,FCST_APR,FCST_MAY,FCST_JUN,
FCST_JUL,FCST_AUG,FCST_SEP,FCST_OCT,FCST_NOV,FCST_DEC
)
)UNPIVOT_HANDLE
),
A_Month_CTe AS
(
SELECT
[L2_ORG_CODE] ,
[L2_ORG_NAME] ,
[L3_ORG_CODE] ,
[L3_ORG_NAME] ,
[YEAR] ,
[JOB] ,
[JOB_NAME] ,
[SUB_JOB] ,
[SUB_JOB_NAME] ,
[EOC_NC] ,
[EOC_NC_DESC] ,
[VALUE],
[COLS]
FROM
(
SELECT * FROM [Cost_Actual]
) UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
ACT_JAN,ACT_FEB,ACT_MAR,ACT_APR,ACT_MAY,ACT_JUN,
ACT_JUL,ACT_AUG,ACT_SEP,ACT_OCT,ACT_NOV,ACT_DEC
)
)UNPIVOT_HANDLE
)
SELECT
T1.[L2_ORG_CODE] ,
T1.[L2_ORG_NAME] ,
T1.[L3_ORG_CODE] ,
T1.[L3_ORG_NAME] ,
T1.[YEAR] ,
T1.[JOB] ,
T1.[JOB_NAME] ,
T1.[SUB_JOB] ,
T1.[SUB_JOB_NAME] ,
T1.[EOC_NC] ,
T1.[EOC_NC_DESC] ,
T1.[COLS] AS F_Months,
T1.[VALUE] AS F_Values,
T2.[COLS] AS A_Months,
T2.[VALUE] AS A_Values
FROM F_Month_CTe T1
INNER JOIN A_Month_CTe T2
ON T1.[L2_ORG_CODE] = T2.[L2_ORG_CODE] AND
T1.[L2_ORG_NAME] = T2.[L2_ORG_NAME] AND
T1.[L3_ORG_CODE] = T2.[L3_ORG_CODE] AND
T1.[L3_ORG_NAME] = T2.[L3_ORG_NAME] AND
T1.[YEAR] = T2.[YEAR] AND
T1.[JOB] = T2.[JOB] AND
T1.[JOB_NAME] = T2.[JOB_NAME] AND
T1.[SUB_JOB] = T2.[SUB_JOB] AND
T1.[SUB_JOB_NAME] = T2.[SUB_JOB_NAME] AND
T1.[EOC_NC] = T2.[EOC_NC] AND
T1.[EOC_NC_DESC] = T2.[EOC_NC_DESC] AND
SUBSTRING(T1.[COLS] , 2, 3) = SUBSTRING(T2.[COLS] ,2, 3)
May 23, 2010 at 10:54 pm
For Cold Coffee
Dear Friend,
Thanks a lot for your uninterrupted support.....
I have just changed the name of the some columns and data is now some 139 records, however, the number of columns and the sequence is the same. When I run on "Now" table it works fine, however, in case of my table it is not working
I'm attaching a sample excel sheet for your reference. I have used the below attached code which I modified as per the new requirement:
;WITH F_Month_CTe AS
(
SELECT
[L2_ORG_CODE] ,
[L2_ORG_NAME] ,
[L3_ORG_CODE] ,
[L3_ORG_NAME] ,
[YEAR] ,
[JOB] ,
[JOB_NAME] ,
[SUB_JOB] ,
[SUB_JOB_NAME] ,
[EOC_NC] ,
[EOC_NC_DESC] ,
[VALUE],
[COLS]
FROM
(
SELECT * FROM [Cost_Actual]
) UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
FCST_JAN,FCST_FEB,FCST_MAR,FCST_APR,FCST_MAY,FCST_JUN,
FCST_JUL,FCST_AUG,FCST_SEP,FCST_OCT,FCST_NOV,FCST_DEC
)
)UNPIVOT_HANDLE
),
A_Month_CTe AS
(
SELECT
[L2_ORG_CODE] ,
[L2_ORG_NAME] ,
[L3_ORG_CODE] ,
[L3_ORG_NAME] ,
[YEAR] ,
[JOB] ,
[JOB_NAME] ,
[SUB_JOB] ,
[SUB_JOB_NAME] ,
[EOC_NC] ,
[EOC_NC_DESC] ,
[VALUE],
[COLS]
FROM
(
SELECT * FROM [Cost_Actual]
) UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
ACT_JAN,ACT_FEB,ACT_MAR,ACT_APR,ACT_MAY,ACT_JUN,
ACT_JUL,ACT_AUG,ACT_SEP,ACT_OCT,ACT_NOV,ACT_DEC
)
)UNPIVOT_HANDLE
)
SELECT
T1.[L2_ORG_CODE] ,
T1.[L2_ORG_NAME] ,
T1.[L3_ORG_CODE] ,
T1.[L3_ORG_NAME] ,
T1.[YEAR] ,
T1.[JOB] ,
T1.[JOB_NAME] ,
T1.[SUB_JOB] ,
T1.[SUB_JOB_NAME] ,
T1.[EOC_NC] ,
T1.[EOC_NC_DESC] ,
T1.[COLS] AS F_Months,
T1.[VALUE] AS F_Values,
T2.[COLS] AS A_Months,
T2.[VALUE] AS A_Values
FROM F_Month_CTe T1
INNER JOIN A_Month_CTe T2
ON T1.[L2_ORG_CODE] = T2.[L2_ORG_CODE] AND
T1.[L2_ORG_NAME] = T2.[L2_ORG_NAME] AND
T1.[L3_ORG_CODE] = T2.[L3_ORG_CODE] AND
T1.[L3_ORG_NAME] = T2.[L3_ORG_NAME] AND
T1.[YEAR] = T2.[YEAR] AND
T1.[JOB] = T2.[JOB] AND
T1.[JOB_NAME] = T2.[JOB_NAME] AND
T1.[SUB_JOB] = T2.[SUB_JOB] AND
T1.[SUB_JOB_NAME] = T2.[SUB_JOB_NAME] AND
T1.[EOC_NC] = T2.[EOC_NC] AND
T1.[EOC_NC_DESC] = T2.[EOC_NC_DESC] AND
SUBSTRING(T1.[COLS] , 2, 3) = SUBSTRING(T2.[COLS] ,2, 3)
May 23, 2010 at 11:20 pm
Deepak, am not being rude here or harsh here buddy, but did u understand what i had coded? Did u understand the logic behind my code? Hmmm, you dint do your home-work well at all...It might cause some trouble when you dont understand the code and implement it in your PROD/Reporting system.. Better understand and them implement your code, sire!
Now that being said, if i dont provide any solution, it would be mean. Here is the code for the Cost_Actual Excel sheet you had attached. I have run the following code against the data in the excel, it worked fine for me! Tell us if that worked for you as well!
Code:
;WITH F_Month_CTE AS
(
SELECT
[L2_ORG_CODE] ,
[L2_ORG_NAME] ,
[L3_ORG_CODE] ,
[L3_ORG_NAME] ,
[YEAR] ,
[JOB] ,
[JOB_NAME] ,
[SUB_JOB] ,
[SUB_JOB_NAME] ,
[EOC_NC] ,
[EOC_NC_DESC],
[COLS],
[VALUE]
FROM
(
SELECT * FROM [cost_actual] -- Replace with your table name
) UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
FCST_JAN,FCST_FEB,FCST_MAR,FCST_APR,FCST_MAY,FCST_JUN,
FCST_JUL,FCST_AUG,FCST_SEP,FCST_OCT,FCST_NOV,FCST_DEC
)
)UNPIVOT_HANDLE
),
A_Month_CTE AS
(
SELECT
[L2_ORG_CODE] ,
[L2_ORG_NAME] ,
[L3_ORG_CODE] ,
[L3_ORG_NAME] ,
[YEAR] ,
[JOB] ,
[JOB_NAME] ,
[SUB_JOB] ,
[SUB_JOB_NAME] ,
[EOC_NC] ,
[EOC_NC_DESC],
[COLS],
[VALUE]
FROM
(
SELECT * FROM [cost_actual] -- Replace with your table name
) UNPIVOT_TABLE
UNPIVOT
( VALUE FOR COLS IN
(
ACT_JAN,ACT_FEB,ACT_MAR,ACT_APR,ACT_MAY,ACT_JUN,
ACT_JUL,ACT_AUG,ACT_SEP,ACT_OCT,ACT_NOV,ACT_DEC
)
)UNPIVOT_HANDLE
)
SELECT
T1.[L2_ORG_CODE] ,
T1.[L2_ORG_NAME] ,
T1.[L3_ORG_CODE] ,
T1.[L3_ORG_NAME] ,
T1.[YEAR] ,
T1.[JOB] ,
T1.[JOB_NAME] ,
T1.[SUB_JOB] ,
T1.[SUB_JOB_NAME] ,
T1.[EOC_NC] ,
T1.[EOC_NC_DESC] ,
T1.[COLS] AS FCST_Months,
T1.[VALUE] AS FCST_Values,
T2.[COLS] AS ACT_Months,
T2.[VALUE] AS ACT_Values
FROM
F_Month_CTE T1
INNER JOIN
A_Month_CTE T2
ON
T1.[L2_ORG_CODE] = T2.[L2_ORG_CODE]AND
T1.[L2_ORG_NAME] = T2.[L2_ORG_NAME]AND
T1.[L3_ORG_CODE] = T2.[L3_ORG_CODE]AND
T1.[L3_ORG_NAME] = T2.[L3_ORG_NAME]AND
T1.[YEAR] = T2.[YEAR]AND
T1.[JOB] = T2.[JOB]AND
T1.[JOB_NAME] = T2.[JOB_NAME]AND
T1.[SUB_JOB] = T2.[SUB_JOB]AND
T1.[SUB_JOB_NAME] = T2.[SUB_JOB_NAME]AND
T1.[EOC_NC] = T2.[EOC_NC]AND
T1.[EOC_NC_DESC] = T2.[EOC_NC_DESC]AND
SUBSTRING(T1.[COLS] , 6, 3) = SUBSTRING(T2.[COLS] , 5, 3)
C'est Pras!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply