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?
I have the following result set but I want to stack or transpose the 3 fields into a single column. I may add more fields later, but right now I want to know what's the best and simplest way.
I am trying to pivot data based on columns value in year column... but results are not showing up correctly. I want to see all columns after pivot.I want to Pivot based on year shown in the data but it can be dynamic as year can go for last 3 years
I am also using an inner join as i have two amount columns in my code and i want to show both amount columns for all displayed year.I am able to pivot but I need in output all the columns like this Id,MainDate, Year1,Year2,Year3(if any), AMT1 for YR1, AMT2 for Yr1, , AMT1 for YR2, AMT2 for Yr2, AMT1 for YR3, AMT2 for Yr3,
Here is some data:
-- CREATE TABLE [dbo].[TEMP]( --[FileType] [varchar](19) NOT NULL, --[dType] [char](2) NOT NULL, --[dVersion] [char](2) NOT NULL, --[Id] [char](25) NOT NULL, --[MainDate] [char](40) NULL,
SELECT Gruppo_Assegnatario, [100] as stato1, [101] as stato2, [102] as stato3 FROM ( select
[Code] ...
That extracts only zeros (columns "stato1", "stato2", "stato3"):
Gruppo_Assegnatariostato1stato2stato3 SDB_BE Vita Antiriciclaggio0 00 SDB_BE Vita Assistenza clienti000 SDB_BE Vita Emissione000 SDB_BE Vita Gestione Rendite000 SDB_BE Vita Liquidazioni000
[Code] ....
Unlike the "SourceTable":
select CASE_ID_, Stato, Gruppo_Assegnatario FROM TicketInevasiPerGruppoEStato extracts
CASE_ID_ Stato Gruppo_Assegnatario HD0000003736734 AssegnatoSDB_GBS Variazione HD0000003736739 AssegnatoSDB_GBS Variazione HD0000003736743 AssegnatoSDB_GBS Variazione HD0000003736783 AssegnatoSDB_GBS Variazione HD0000003736806 SospesoSDB_BE Vita Selezione
[Code] ....
How can I get the right count in the first data mining replacing the zeros (columns "stato1", "stato2", "stato3")?
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
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
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
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...
Hi all,Many times would be useful (for me at least) if sum() could summarizetextual fields by simply concatenating them :eg a table named 'lessons' containsLesson Teachermath Mr. Brownhistory Mr. Brownmath Ms. Whitegym Mr. Greengeo Ms. Whiteso I could use a select like this:select Lesson,sum(Teacher) group by Lesson(and sum might take a second parameter as a separator between added fields)Of course, these things aren't exists so I wanted to write my own"textsum()" function, but in MSSQL2000 user functions can take onlyscalar variables AFAIK.Anyone has faced and/or has solved this problem other way (with a simpleselect)?Thankszf
I have a table imported from a legacy Oracle database that stores values vertically in name/value pairs. I store it in table-type variable that is an exact copy of the structure:
If this information were pivoted horizontally: OMNI_NUMBER would be the primary key.
TIMESTAMP is a 10-digit integer that represents the number of seconds since 1/1/1970 UTC that requires additional conversion. DATA_TYPE is not the data type. It is a general categorization of the next two columns.PARAMTER would be the column headings if it were horizontal..PARAMETER_VALUE would be the data value in that column.
I would like to try to use PIVOT to list the PARAMETER column values as column headers. This seems to work fine. What's confusing me is that I'd like it to list the PARAMETER_VALUE column values as raw data, just as it is in the source version, without having to apply some sort of aggregate function to it. Here's a CSV sample of the data you can paste into Excel. I'm trying to transform this:
1) Production data with column headers: Key, Facility, Line, Time, Output 2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key Facility Line Time Output Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
Can we use case in pivot like below? I am getting an error. I want to do Pivot on condition basis.
select ( Column1 ,Column2 ,Column3 ,Column4 ,coloumn5 from Mytable ) x pivot ( case when Column1 = 6 then sum(Column3) else max(Column4) End for coloumn5 in (' + @COLS + ') )p
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I try the SQL script below but it returning 6 rows instead of 3–
I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales 01-Jan-14,100,120,300 02-Jan-14,80,150,300 03-Jan-14,100,20,180
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
Instead of doing a Count for the Pivot (the count will always be either 0 or 1 due to the design of the table being used), I would like to return an "X" for those records with a count of 1, and return a blank (otherwise null) for those records with a count of 0. So, the result set would look like:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast 1000 Item1000 X X X X X 1024 Item1024 X X X X X 135 Item135 X X X X X 107 Item107 X X X 106 Item106 X X X X X
I tried using a Case statement within the PIVOT portion, but I either did it incorrectly or it's not possible to do use a Case within the Pivot. Can I easily accomplish this?
Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
I have a pivot table that connects to our data warehouse via a PowerPivot connection. The data contains a bunch of comment fields that are each between 250 and 500 characters. I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.
However, whenever I refresh the data, the text wrapping un-sets itself. Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text. This is very burdensome on the user, and degrading the experience.
Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?
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):
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error: Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18 Incorrect syntax near the keyword 'external'. CREATE FUNCTION clrDynamicPivot ( -- Add the parameters for the function here @query nvarchar(4000), @pivotColumn nvarchar(4000),
