T-SQL (SS2K8) :: Transpose / Pivot Textual Data

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


ADVERTISEMENT

Transact SQL :: Transpose (Pivot) Columns To Rows?

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

Attempting To Transpose/pivot Columns To Rows... I Think.

Jun 26, 2006

I have something of a challenge for you folks (at least it's very challenging for me).

I have a table that has data that looks like this:

Date______OrderNum____WorkDay

2006-06-1__AA_________1

2006-06-1__AB_________1

2006-06-2__BA_________2

2006-06-2__BB_________2

2006-06-2__BC_________2

2006-06-5__CA_________3

2006-06-5__CB_________3

etc.

So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.

What I am trying to write is a SQL statement that will display results thus:

Day1______Day2______Day3_______etc.

AA________BA_______CA_________etc.

AB________BB_______CB_________etc.

Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.

I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!

 

View 7 Replies View Related

SQL Server 2012 :: Transpose (Pivot) Columns To Rows?

Aug 15, 2015

I need to pivot my records in columns into rows. Here is sample data:

create table #columnstorows
(
Payervarchar (5),
IDvarchar (5),
ClMvarchar (2),
Paid1float,

[Code] ....

Desired result:

Payer1Payer2ID1ID2Paid1APaid1BPaid2APaid2BPaid3APaid3B
U001 U002 00100276.58 19.53 153.4896.43 53.48 200

View 6 Replies View Related

T-SQL (SS2K8) :: Possible Pivot / CTE Recursion Restructuring Of Data

Sep 7, 2015

I have a table (folderstructure) with the following columns:

pcmid, cmid, foldername
pcmid is the parent directory
cmid is the directory
foldername is the name of the directory

e.g. note, number of levels are unknown

cmid pcmid name
1 NULL c:
101 1 level1
201 101 level2
45 101 level2a
56 201 level3
57 201 level3a

I'm looking to create a table that has cmid followed by the full directory path

So either (using above):

cmid path
1 c:
101 c:level1
201 c:level1level2
45 c:level1level2a
56 c:level1level2level3
57 c:level1level2level3a

etc.

OR

cmid 1 2 3 4
1 c:
101 c: level1
201 c: level1 level2
45 c: level1 level2a
56 c: level1 level2 level3
57 c: level1 level2 level3a

etc.

I've can use recursion to allocate a level to each name /cmid/pcmid combination

I could use multiple self joins

Is there a way this can be achieved using pivots or CTE recursion or something else...

View 2 Replies View Related

T-SQL (SS2K8) :: Stack / Transpose One Row With 3 Fields To A Single Column?

Apr 29, 2015

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.

Current result set:

TotalAllCustomersOnFile | TotalConsumersWithValidEmail | TotalConsumersWithValidEmailAndOptedIn
2,500 1,750 1,500

Desired result set:

Audience | Totals
----------------------------------------------------
TotalAllCustomersOnFile | 2,500
TotalConsumersWithValidEmail | 1,750
TotalConsumersWithValidEmailAndOptedIn | 1,500

View 3 Replies View Related

T-SQL (SS2K8) :: Pivot Data Based On Columns Value In Year Column

Jun 4, 2014

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,

[code]....

View 5 Replies View Related

T-SQL (SS2K8) :: Pivot Table - How To Get Right Count In First Data Mining Replacing Zeros

Mar 24, 2014

I run this code:

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")?

View 5 Replies View Related

T-SQL (SS2K8) :: Pivot Query - Convert Data From Original Table To Reporting View

Apr 8, 2014

I want to convert the data from Original Table to Reporting View like below, I have tried but not get success yet.

Original Table:
================================================================
Id || Id1 || Id2 || MasterId || Obs ||Dec || Act || Status || InstanceId
================================================================
1 || 138 || 60 || 1 || Obs1 ||Dec1 || Act1 || 0|| 14
2 || 138 || 60 || 2 || Obs2 ||Dec2 || Act2 || 1|| 14
3 || 138 || 60 || 3 || Obs3 ||Dec3 || Act3 || 1|| 14
4 || 138 || 60 || 4 || Obs4 ||Dec4 || Act4 || 0|| 14
5 || 138 || 60 || 5 || Obs5 ||Dec5 || Act5 || 1|| 14

View For Reporting:

Row Header:
Id1 || Id2 || MasterId1 || Obs1 ||Desc1 ||Act1 ||StatusId1||MasterId ||Obs2 ||Desc2 ||Act2 ||StatusId2 ||MasterId3||Obs3 ||Desc3 ||Act3 ||StatusId3||MasterId4||Obs4||Desc4 ||Act4 ||StatusId4 ||MasterId5||Obs5 ||Desc5 ||Act5 ||StatusId5||InstanceId

Row Values:
138 || 60 || 1 || Obs1 ||Desc1 ||Act1 ||0 ||2 ||Obs2 ||Desc2||Act2 ||1 ||3 ||Obs3||Desc3 ||Act3 ||2 ||4||Obs4||Desc4 ||Act4 ||0 ||5 ||Obs5 ||Desc5 ||Act5 ||1 ||14

View 6 Replies View Related

Transpose Data Using SQL

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

Data Transpose - Need Help

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

A Query-Transpose Of Data

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

Trying To Transpose And Collapse Data...

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

Transact SQL :: Transpose Data Update

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

Sum() For Textual Fields?

Jul 20, 2005

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

View 2 Replies View Related

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 View Related

T-SQL (SS2K8) :: PIVOT Without Aggregation

Jun 28, 2015

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:

DECLARE @OracleEngData TABLE
( DataSourceCHAR(8)
, [OMNI_NUMBER] INTEGER
, [TIMESTAMP] INTEGER
, [DATA_TYPE] NVARCHAR(24)
, [PARAMETER] NVARCHAR(32)
, [PARAMETER_VALUE] NVARCHAR(132));

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:

OMNI_NUMBER,TIMESTAMP,DATA_TYPE,PARAMETER,PARAMETE_VALUE
506026,1413240436,test_data,cnr,211250000,54.8
506026,1413244259,test_data,cnr,211250000,53.2
506026,1413244679,test_data,cnr,211250000,53.1
506026,1413309646,test_data,cnr,211250000,53.4
506026,1413315987,test_data,cnr,211250000,53

[code]...

But I don't want the sum of the values or the average of the values, just the values. The PIVOT syntax seems to require an aggregate operation.

View 2 Replies View Related

Power Pivot :: One Slicer To Control Two Pivot Tables That Have Different Source Data And Common Key

Jul 8, 2015

I have two data tables:

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.

View 5 Replies View Related

Power Pivot :: Force Measure To Be Visible For All Rows In Pivot Table Even When There Is No Data?

Oct 13, 2015

Can I force the following measure to be visible for all rows in a pivot table?

Sales Special Visibility:=IF(
    HASONEVALUE(dimSalesCompanies[SalesCompany])
    ;IF(
        VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
        ;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
        ;[Sales]
    )
    ;BLANK()
)

FYI, I also have other measures as well in the pivot table that I don't want to affect.

View 3 Replies View Related

T-SQL (SS2K8) :: Pivot And Unpivot In Same Query?

Feb 15, 2014

I have below table and within same query i need pivot and unpivot.

create table #temp(name1 varchar(10),name2 varchar(10),name3 varchar(10),month date,emp1 int,emp2 int,emp3 int,emp4 int)
insert into #temp values ('a','b','c','1-1-2013',1,2,3,4)
insert into #temp values ('a','b','c','1-2-2013',11,20,30,40)
insert into #temp values ('a','c','c','1-1-2013',22,30,80,40)
insert into #temp values ('a','c','c','1-2-2013',28,34,39,30)
select * from #temp

Now i need output in below format

name1,name2,name3,Emp,jan-13,feb-13
a,b,c,emp1,1,11
a,b,c,emp2,2,20
a,b,c,emp3,3,30
a,b,c,emp4,4,40
a,c,c,emp1,22,28
a,c,c,emp2,30,34
a,c,c,emp3,80,39
a,c,c,emp4,40,30

View 4 Replies View Related

T-SQL (SS2K8) :: Using CASE Statement Within A PIVOT

Jun 17, 2014

I am using a PIVOT function to obtain the Invoice Values, but they appear in different currencies so need to perform a case function.

But am struggling with the syntax;

This fails a syntax check with
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'Case'.

[Code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Using Case Statement In Pivot?

Jun 23, 2015

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

View 2 Replies View Related

AGGREGATE Doesn't Do MIN/MAX On Textual Columns

Jun 1, 2006

Hi,

Can anyone from MS exaplain why the AGGREGATE component doesn't allow you to select MIN/MAX when the column is DT_STR/DT_WSTR?

Thanks

Jamie

View 12 Replies View Related

T-SQL (SS2K8) :: Incorrect Syntax Near Keyword (pivot)

Oct 8, 2014

I'm struggling with one Syntax error

CREATE TABLE #ToolCompliance
(
SOFTWAR_ID INT
,CONTROL_CODE VARCHAR(100)
,CONTROL_STATUS VARCHAR(100)
)
INSERT INTO #ToolCompliance
VALUES(1000,'AC','SUCCESS')

[code]....

View 4 Replies View Related

T-SQL (SS2K8) :: Join Or Pivot / Unpivot For Mismatch Dates

Jul 31, 2014

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–

select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
,[ineffective_date]

[code]....

View 3 Replies View Related

T-SQL (SS2K8) :: Change Column Order In Dynamic Pivot?

Sep 17, 2014

I am creating dynamic pivot and my column order is as below

[2015-02],[2015-04] [Prior] ,[2014-08],[2014-11]

but i want to display as below:

[Prior],[2014-08],[2014-11],[2015-02],[2015-04]

View 1 Replies View Related

T-SQL (SS2K8) :: Pivot When Don't Know Amount Of Columns And Column Names

Jan 7, 2015

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

Turned to this

01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180

Or even just the date and a SUM

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?

View 2 Replies View Related

T-SQL (SS2K8) :: Using Case Within PIVOT Portion Of Crosstab Query

Apr 13, 2015

I have created a crosstab query using the Pivot statement that returns the expected results. The results look similar to the sample below:

ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast

1000 Item1000 1 0 0 1 0 1 0 1 1 0
1024 Item1024 1 0 0 1 0 1 0 1 1 0
135 Item135 1 0 0 1 0 1 0 1 1 0
107 Item107 0 0 0 0 0 1 0 1 1 0
106 Item106 1 0 0 1 0 1 0 1 1 0

I'm using this statement to create the result set:

SELECT ItemKey, Description, Aflatoxin, [Coliform Bacteria], [E_Coli],[Fumonisin],
Melamine,Moisture, Mold, Salmonella, [Vomitoxin (DON)], Yeast
FROM
(SELECT tblInventory.ItemKey, tblInventory.Description,
jctProductClassificationRequiredTest.ProductTestClassID, tlbTestType.TestDescription

[Code] .....

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?

View 6 Replies View Related

T-SQL (SS2K8) :: Get Current DB Backup Setup List In Pivot Style?

Dec 1, 2014

I need to list the current DB Backup Set up list in PIVOT STYLE.

I need following way:

Database_NameFULL - DDIFF - ILOG - L
DB1DL
DB2D
modelDL
DB3DIL
msdbD

View 2 Replies View Related

Power Pivot :: Auto Refresh Excel Table (Not Pivot Table) Using Data Source

Jul 8, 2015

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 ...)

View 2 Replies View Related

Power Pivot :: Pivot Table Loses Text Wrapping For Text Data Upon Refresh

Apr 29, 2015

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?

View 2 Replies View Related

Transpose Source Data From A System Via Metadata Lookup Table Into Destination Table

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

T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot

Mar 9, 2015

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),

[code]....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved