Transact SQL :: How To Transpose Data (Yearly Financials)
Jun 1, 2015
I have a simple table of yearly financials to transpose:
Year/Field      Financial1 Financial2 Financial3...
2014Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
2013Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
2012Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
....
The number of years available is dynamic. I would like to retrieve it as follows:
                    2014          2013       2012    Â
.....
Financial1Â Â Â Â 1000000Â Â 2000000Â 3000000
Financial2Â Â Â Â 1000000Â Â 2000000Â 3000000
Financial3Â Â Â 1000000Â Â 2000000Â 3000000$
What is the simplest way to achieve this?
View 7 Replies
ADVERTISEMENT
Jul 15, 2015
I have following sample data
I want this data updated to different table based on PK value relationship like below,
What shall I do to make such update ? Here is the sample script I want ot use
 DECLARE @Activity TABLE(PK BIGINT,Value1 NVARCHAR(10),Value2 NVARCHAR(10))
 INSERT INTO @Activity(PK) VALUES(1)
 INSERT INTO @Activity(PK) VALUES(2)
 INSERT INTO @Activity(PK) VALUES(3)
[Code] .....
View 11 Replies
View Related
Nov 23, 2015
Having table like below. Here want to cumulative the values quarter and half yearly wise...
declare @table tableÂ
(month varchar(10),
value int)
insert into @table values('apr' ,100 )
insert into @table values('may' ,200 )
insert into @table values('jun' ,300 )
[Code] ....
Like wise the data should added...
View 3 Replies
View Related
Jun 29, 2015
I wondered if it is possible to convert/transpose a list into a 1 row. I have attached a small sample of data and what outcome I would like.
View 19 Replies
View Related
Aug 15, 2015
I need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows
(
Payer varchar (5), Â Â Â Â Â Â Â Â Â
ID varchar (5), Â Â Â
ClM varchar (2), Â Â Â Â Â Â Â Â Â
Paid1 float,
Paid2 float,
Paid3 float
) Â Â Â Â Â Â Â Â Â Â Â
[code]....
Desired result:
Payer1 Payer2
ID1 ID2
Paid1A Paid1B
Paid2A Paid2B
Paid3A Paid3B
U001 U002
 001 002
76.58 19.53
 153.48 96.43
 53.48  200
View 10 Replies
View Related
Apr 10, 2003
Hi all,
I have the Following Query
It is working fine if the first month is 01 and Second month is 12
but i want like this if the user selects 200103 to 200203 i want the
value of composite should be added from 200103 to 200203 in one row
and other row from 200203 to 200303.
like this if the user selects from 200004 to 200304 i have to get 3 rows..
that is Yearly data..so how can i modify the following query or any new query.
select CONVERT(CHAR(4),period,112), sum(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between '200101' and '200312'
group by CONVERT(CHAR(4),period,112)
order by CONVERT(CHAR(4),period,112)
Thanks
View 2 Replies
View Related
Aug 31, 2015
Below. I have also pasted the current result of this query and the desired result.Â
Query can be updated to get the desired result as given below?
Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3
5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01
Using DB2.
View 2 Replies
View Related
Jul 26, 2006
Hi Guys
I am having a bit of a delimma and am wondering if there is someone out there that could suggest how i could write a SQL statement that would alow me to return data in a certain way,
I have data as follows...
FIrst problemi is the Date in the LogDate COlumn is of Text Type not Date..
Second problem is i need to take this data and transform it to look like the table below
RCDIDEmployeeIDLogDateLogTimeTerminalIDInOut
411 07/23/200620:45:02iGuard# IN
421 07/23/200620:46:17iGuard# OUT
431 07/23/200620:48:08iGuard# IN
441 07/23/200620:48:18iGuard# OUT
451 07/23/200620:48:24iGuard# IN
461 07/23/200620:48:30iGuard# OUT
471 07/23/200620:48:36iGuard# IN
481 07/23/200620:48:41iGuard# OUT
501 07/23/200620:49:57iGuard# IN
511 07/23/200620:50:14iGuard# OUT
521 07/23/200620:59:34iGuard# IN
531 07/23/200620:59:40iGuard# OUT
Employee IDDateInOutIn OutTotalTimeIn
123/07/200620:3520:3620:3820:3900:02
Basicaly i need to transpose it..
If anyone has the sql or knows the sql on how to do this i would be very greatful
View 3 Replies
View Related
Jan 20, 2004
Hi all,
Help me out, i am trying to get all values in Table A and insert into table B, i though of writing cursor. see endof the message
Table A:
CREATE TABLE [dbo].[M_SCANNEDSURVEY_AP] (
[M_CustomerSurveyID] [bigint] NULL ,
[SurveyID] [bigint] NULL ,
[LoadStatus] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[1] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[4] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[5] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[6] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[7] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[8] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[9] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[10] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[11] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[12] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO
Table B:
CREATE TABLE [dbo].[M_RESPONSE] (
[M_CustomerSurveyID] [bigint] NOT NULL ,
[SurveyID] [bigint] NOT NULL ,
[SeqNumber] [bigint] NOT NULL ,
[Response] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO
I want to insert everything in table A to table B
M_CustomerSurveyID -> M_CustomerSurveyID
SurveyID -> SurveyID
I will hardcode insert 1,2,3,4,5,6,7,8,9,10,11,12 for sequence number
values of 1,2,3,4,5,6,7,8,9,10,11,12 -> Response
DateSubmitted -> DateSubmitted
Declare @Count_Scan INT
BEGIN
-- Get the count of scanned data in scanned data table with LoadStatus = N
SELECT @Count_Scan = COUNT(*) from M_SCANNEDSURVEY_APTEST where
LoadStatus = 'N'
IF @Count_Scan > 0
BEGIN
DECLARE ScanData_Cursor CURSOR FOR
SELECT * FROM M_SCANNEDSURVEY_AP WHERE LoadStatus = 'N'
OPEN ScanData_Cursor
FETCH NEXT FROM ScanData_Cursor
While (@@Fetch_Status <> -1)
Begin
If (@@Fetch_Status = -2)
Begin
FETCH NEXT FROM ScanData_Cursor
Continue
End
CLOSE ScanData_Cursor
DEALLOCATE ScanData_Cursor
END
SET @CountSuccess = 'Y'
END
View 4 Replies
View Related
Jul 23, 2005
I am supplying you with Sample Data:-Initial Classcode SampleSize Average------- ---------- -------------------------------ADK SSC 22 3.6800000000000002ADK TSC 17 2.7599999999999998ADK TSM 5 3.5499999999999998ANB FCA 31 3.23ANB FCB 50 3.0499999999999998ANB FCC 30 3.0899999999999999ANB SCA 35 3.02ANB SCB 9 3.4300000000000002ANB TCA 30 2.77ANB TCB 6 1.8799999999999999APG MCH 10 3.8300000000000001APG TSCH 9 4.21AUG FCC 30 3.5499999999999998AUG SCA 28 2.7800000000000002AUG SCB 29 3.4300000000000002AUG SCC 30 2.8999999999999999AUG TCA 30 2.8599999999999999AUG TCB 29 2.1200000000000001AVK TSP 12 3.6200000000000001BKK FS 32 2.52BKK TSM 5 3.3799999999999999BSK SSP 28 3.1200000000000001BSK TSP 12 3.0600000000000001------- ---------- -------------------------------These are the averages of teachers grouped by initial.Maximum 7 averages are related to each teacher.Ignore the column SampleSize.Using this output is it possible to get output like this:Initial Class1 Avg1 Class2 Avg2 Class3 Avg3 Class4 Avg4..ADKTSA 1.4 TSB 2.5 TSC 4.5 SSC 5.0..ANB SSA 1.4 SSB 2.5 NULL NULL NULL NULL..APG.........................AVK....................BKK....................BSK.....................Since the maximum class nos are 7, those having less than classes willcontain NULL in the class and average field.Is it possible to carry out this in single query?-Sameer
View 1 Replies
View Related
Oct 4, 2006
I'm relatively new to this, so bear with me here. (SQL Server 2005 Express, Datatypes are all varchar, int or money, nothing crazy...)
I currently have a table (not designed by me...) which looks like this:
ProjectID Months Expenses
3214 JAN 45.67
3214 MAR 56.78
1234 JAN 78.99
4567 MAY 43.56
And so on.... And I need this:
Project ID Jan Feb Mar Apr May Jun etc....
3214 45.67 56.78
1234 78.99
4567 43.56
I had attempted to do it using this code (really sloppy... I know. Beginner's attempt...)
DECLARE @Months varchar(4)
DECLARE @Counter int
DECLARE @Rowcount int
EXEC @RowCount=dbo.ReturnRowCount
SET @Counter = 0
WHILE @Counter <= @RowCount
BEGIN
SET @Counter=@Counter+1
SET @Months=(SELECT Months FROM TestData WHERE RowNum=@Counter)
SELECT @Months
WHILE @Months='JAN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jan)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='FEB'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Feb)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Mar)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='APR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Apr)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAY'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, May)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jun)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUL'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jul)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='AUG'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Aug)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='SEP'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Sep)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='OCT'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Oct)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='NOV'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Nov)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='DEC'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Dec)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
SET @Months=''
END
SELECT RowNum, ProjectID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec FROM SusansOutputTable
I also tried using IF statement instead of the imbedded WHILE statement and had the same result. What it's returning is 98,765 results of the same thing, with the exception of the last 25 rows, which return what I expected (which still needs to be collapsed...) I know there HAS to be an easier way to do this, I'm afraid it might be a bit beyond me though, any help?
Ps. ReturnRowCount function just returns the row count of the base table, assuming a rowid column with an IDENTITY int variable, which I can safely assume in this case. Also, the counter seems to work fine, but something is wrong in my internal portion of the while loop I think...
View 3 Replies
View Related
Jan 19, 2015
In our contract management system, each contract has over 100 reference fields attached to it. These are all stored in single table with contract ID, reference GUID and value as the columns.
So you will have multiple rows for each contract....one for each of the reference fields and then the value attached to that reference.
I want to return the data so there is one row per contract with the reference fields as columns and the reference field values as the column data.
Can this be done using PIVOT as I have tried but not had any success?
View 6 Replies
View Related
Oct 15, 2007
I have a SQL database which uses Access 2000 as the front end to do all queries. Currently i have a query that lists the amount an employee has spent on purchasing products i.e.:
Employee Name - Code - Description - Unit - Price - Month
Joe Bloggs - zzz - Pack of 10 CDRs - 1 - £3.99 - October
I would like to add another column that lists how much theyve spent so far in the current year. The employee name and Month has a parameters of =@Employee and =@Month so when the query is run i can enter the name of the employee and the month which then displays the results.
I tried to create a query where i entered Month under column (in access) followed by Total as this is what i used to do in an Access database but didnt work - how could the same be achieved in SQL?
Thanks in advance
View 10 Replies
View Related
Apr 1, 2014
I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.
Source Data Table:
Table Name: Source
SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013
Metadata Table:
Table Name:Metadata
MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date
Destination table:
The source data to be loaded into the destination table(as shown below):
Table Name: Destination
SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013
View 7 Replies
View Related
Apr 13, 2012
I am attempting to sum the gas production for each organization number, and separate gas production into each year as such:
Organization_Number20082009201020112012
103 774 7313868470
But currently I am having trouble displaying this, and it's coming out as such:
Organization_NumberMonth20082009201020112012
103 1 774731386847NULL
103 2 810656654674NULL
202 1 27262702293725122048
202 2 2913205120202064NULL
I want to remove the month and have one total per organization number, as well as remove NULL as show as 0.
Code:
select *
from yearproduction
pivot(
sum(Gas_Prod)
for Year in ([2008], [2009], [2010], [2011], [2012]))
as YearlyProduction
order by Organization_Number, Month
View 1 Replies
View Related
Feb 25, 2014
I'm using this query to to calculate yearly finance values.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance]
Now I need to multiply the [FinanceValue Variance] * 2.50 and for that how can I use the alias name as column in the query. I tried this but it says invalid column name.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance], [FinanceValue Variance] * 2.50 as [NewVariance] from Finance
SumofVariance output will be like 5690.5893656 Also how can I show the SumofVariance to round off 4 decimal places like this 5690.5894.
View 1 Replies
View Related
Jul 2, 2015
I have to calculate the Total number of days present and absent for a singel student.
AS of now i have 3 tables.
1.Daily attendance - Columns -[Guid][,AcademyId],[StudentId],[Date],[Status],[Reason]
2.Student details - Columns - [Guid],[FullName],[DOB],[Address]
3.Class Details - Columns - [Guid],[AcademyId],[Class],[Section],[Startdate],Enddate]
So now i have loaded all the data into the table.
I can fetch the counts for total present and absent
Query i have tried is
Declare
@StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458'
Begin
select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
View 7 Replies
View Related
May 28, 2008
Hi
I am very new to analysis services and using MDX.
I want to select data from a cube using an MDX statement and show the data on a graph report.
I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.
Then set the 'Date' Column to the x-axis and the Value column to the y-axis.
The user also must have the option to not show certain periods (Switch of daily and weekly)
My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.
----
Code
SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,
NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,
[Measures].[ValueAfterLogic],DESC)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS
FROM [Workplace])
WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The case statement will take paramter values when finished
----------------end of code portion
Is this possible or is it suppose to 'split' the columns when moving to SSRS.
Thans in advance
Dev environment - SQL 2008 Feb CTP, VS 2008
View 5 Replies
View Related
Jan 7, 2011
Currently, I have a report that takes two parameters: Â StartDate and EndDate. Â
I would like to schedule the report to run on a Daily, Weekly, Monthly or Yearly basis, but this doesn't work too well with StartDate and EndDate because the parameter is static. Â What is the most elegant way to implement this change?
View 5 Replies
View Related
May 2, 2008
Hello,
I have a table that looks like this:
Customer Treas
------- -----
1010 Merch
1010 Price
1020 Merch
1030 Promo
And I need to add a period column so that the table looks like this.
Customer Treas Period
------- ----- ------
1010 Merch 1
1010 Merch 2
1010 Merch 3
1010 Price 1
1010 Price 2
1010 Price 3
1020 Merch 1
1020 Merch 2
1020 Merch 3
1030 Promo 1
1030 Promo 2
1030 Promo 3
etc. Of course there are 12 periods.
I'm thinking this is related to cross-tab or transpose function somehow? Once again I am clueless to the point I find it difficult to even research the problem on my own.
Hopefully this makes sense.
Thanks!
View 1 Replies
View Related
Apr 26, 2001
Besides using a cursor, is there a way to transpose a short result set to a comma delimited list?
For example, assume SELECT color FROM [tblColor] yields:
Red
Green
Blue
Black
My desired result is really:
Red, Green, Blue, Black
Any clues?
View 1 Replies
View Related
Feb 25, 2004
Hello
I am currently importing an excel table into sql server in the form
date code header1 header2
2004 5AX num1 num2
2004 5AZ num3 num4
......
I am reading this into an sql table fine. However I want to convert its format into the following
date code field value
2004 5AX header1 num1
2004 5AX header2 num2
2004 5AZ header1 num3
2004 5AZ header2 num4
......
The number of header1, header2,..... is dynamic so I cannot hard code the number of headers there are.
Has anyone got any ideas. I have got some code already which will give me the number of headers there are but that Im afraid is as far as I have got before I get stuck
Thanks in advance for any help
Richard
View 2 Replies
View Related
Mar 8, 2007
How to transpose a table. My table is like
taskid date1 hrs1 date2 hrs2 date3 hrs3 emp
1 3/3/2007 8.0 3/4/2007 8.5 3/5/2007 8.0 AAA
2 3/3/2007 8.0 3/4/2007 8.5 3/5/2007 8.0 BBB
I want the result as
date1 date2 date3
emp taskid hrs1 hrs2 hrs3
View 5 Replies
View Related
Jul 23, 2005
I'm playing around with the following tables and need some advice.TablePeople (5000 records)peopleIDsexageraceTableExam (5000 records)examIDdatepeopleIDTableQuestions (5000 records)questionIDquestionFieldname (QFN0001,QFN0002,QFN0003, ... ,QFN5000)TableAnswers (25,000,000 records)examIDquestionIDanswerSQL Query Statement Needed!Query the database to return fields from TablePeople and TableAnswers(from 1 to 255 questionFieldnames)Example Query and return data from:ExamID,sex,age,race,QFN0001,QFN0002,QFN0003,QFN000 5,QFN0007,QFN0011I know how to join the tables and return the following recordsetExamID, sex, age, race, QuestionFieldName, AnswerID100001,M,40,White,QFN0001,0100001,M,40,White,QFN0002,5100001,M,40,White,QFN0003,6100001,M,40,White,QFN0005,3100001,M,40,White,QFN0007,5100001,M,40,White,QFN0011,4100002,M,55,White,QFN0001,1100002,M,55,White,QFN0002,4100002,M,55,White,QFN0003,3100002,M,55,White,QFN0004,4100002,M,55,White,QFN0007,5100002,M,55,White,QFN0011,6etc105000,M,48,White,QFN0001,5105000,M,48,White,QFN0002,4105000,M,48,White,QFN0003,6105000,M,48,White,QFN0005,3105000,M,48,White,QFN0007,4105000,M,48,White,QFN0011,5We would like the resulting table to look like the following:ExamID,sex,age,race,QFN0001,QFN0002,QFN0003,QFN000 5,QFN0007,QFN0011100001,M,40,White,0,5,6,3,5,4100002,M,55,White,1,4,3,4,5,6etc105000,M,48,White,5,4,6,3,4,5Any suggestions appreciated.Gregory S. MoyInformation Processing ConsultantEpiSense Research ProgramDepartment of Ophthalmology & Visual SciencesUniversity of Wisconsin - Madison
View 1 Replies
View Related
Dec 11, 2007
Is there anything like transpose in SSRS.
The result of my t-sql queries gives me many columns and less rows
i.e there are four quaters as rows and there are around 10 columns like sales , customer , material ....
So if i can transpose columns to rows and rows to columns some how then the report will be more presentable according to me.
Can anyone please help me out.
View 1 Replies
View Related
Mar 30, 2007
Hi
I have a "transaction" table of diagnostic tests, each row consisting of one test performed on one person as follows:
Patient Test Time
ID ID of Test
--------------------------
1 1 May 1
1 2 May 1
1 4 May 2
2 5 etc.
3 1
3 3
4 4
4 2
I'd like to transpose this into a table with one row per patient, so I can then describe, for each patient, the sequence of tests that he or she had, e.g.
Patient T1 T2 T3 T4 T5 TEST_SEQUENCE
1 1 2 4 - - "1 2 4"
2 5 - - - - "5"
3 1 3 - - - "1 3"
4 4 2 - - - "4 2"
How do I do this in SQL, preferably without having to specify the maximum number of tests? (In a statistical package we use, this is called a table "reshape" operation)
Thanks in advance.
View 7 Replies
View Related
Oct 22, 2007
Any of you have code to transpose rows into columns in T-SQL 2000. For example I have the following table
ID type
1 A
1 B
2 A
2 C
2 D
etc,....
I want to tranpose
to some like
ID type
1 A,B
2 A,C,D
etc,...
or
ID type1 type2 type3
1 A B
2 A C D
etc,....
Really appreciate any input.
View 3 Replies
View Related
Apr 23, 2008
I have the below table:
Pallet1 ItemA1
Pallet1 ItemB1
Pallet1 ItemC1
Pallet1 ItemD1
Pallet1 ItemE1
Pallet2 ItemA2
Pallet2 ItemB2
Pallet2 ItemC2
Pallet2 ItemD2
Would like to Transpose it to
Pallet 1 ItemA1 ItemB1 ItemC1 ItemD1 ItemE1
Pallet 2 ItemA2 ItemB2 ItemC2 ItemD2 NULL
Would be thankful for Any help.
View 2 Replies
View Related
Jul 2, 2014
I am looking to transpose a row into multiple columns. I have a member's data who might be associated with multiple labs for that one member. Instead of having multiple records for that member, I would like to have one record per member with multiple rows for the lab data.
View 16 Replies
View Related
Mar 30, 2006
I have recordsID Sku Name Date2 41 Blair 01/04/033 45 John 03/04/03that should look like...ID 2 3Sku 41 45Name Blair John.....and so on. Number of source rows will be fixed (12) so no of targetcolumns will be 12 too.Anyone know of a quick way to do this via simple SQL ?
View 3 Replies
View Related
May 29, 2007
Hi Friends,How to transpose the columns into rowsi want to convert the table which looks like thisID Name HomePhone WorkPhone Email1 test1 678364 643733 Join Bytes!2 test2 678344 643553 Join Bytes!to a table which should look like thisID Name Device1 test1 6783641 test1 6437331 test1 Join Bytes!2 test2 6783442 test2 6435532 test2 Join Bytes!Thanks in AdvanceArunkumar
View 1 Replies
View Related
Mar 23, 2015
how to transpose rows to columns . there are 800 dynamic rows
device_idUserNameipaddrmacaddrLastConnecttimeOperatingSystemRAMCPUTOTALSIZEFREESPACEpercentageDriveNameName
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 ActiveX [15.0.0.239]
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 Plugin [15.0.0.239]
[code]...
View 9 Replies
View Related
Oct 29, 2015
I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working.
I have data in the form of:
Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,
The output should look like:
Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black
View 2 Replies
View Related
