Transact SQL :: How To Convert Row To Column With Rows Dynamic

May 21, 2015

I want to convert row to columns with rows dynamic. I have two table, I'll join them and get data. See at

Ok , I try convert rows to columns with PIVOT . i want the result as

GROUP_MOD_IDADDEDITDELETE
G02101

CREATE PROC GET_FUNC @GID CHAR(5)
AS
BEGIN
SELECT FUNCTION_MOD_NAME

[Code] ....

But it's error

Msg 156, Level 15, State 1, Procedure GET_FUNC, Line 13
Incorrect syntax near the keyword 'FOR'.

Note: Type of ALLOW is bit and rows FUNCTION_MOD_NAME is dynamic

View 2 Replies


ADVERTISEMENT

Transact SQL :: Selecting Rows As Column Name And Other Column Values As Rows

Jun 25, 2015

I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.

View 28 Replies View Related

Transact SQL :: Convert Rows Into Columns?

Oct 8, 2015

How can I convert the following data

Create Table tbl_Customer_Bank_Details
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo] varchar(50)
)

[Code] ...

into the following format

Create Table tbl_Customer_Bank_Details2
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo1] varchar(50),

[Code] ....

I am looking at this [URL]

how to do this in SQL 2000.

View 16 Replies View Related

Transact SQL :: Convert Rows To Columns?

Apr 24, 2015

i have a table with dob and test results , i am trying to pull the data from the table and converting rows columns , below is the table i am using . i used to pivot to do this .

create table #TEST_RESULTS
(ID INT,NAME VARCHAR(10),DOB DATETIME,DAYS_SINCE_BIRTH_TO_TEST INT,TEST_RESULTS INT )
INSERT INTO #TEST_RESULTS
VALUES(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 1 ,3)
,(1,'A','2015-01-01' , 2 ,6)

[code]...

View 9 Replies View Related

SQL Server 2012 :: Convert Rows To Columns Using Dynamic PIVOT Table

Feb 3, 2015

I have this query:

SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],

[Code] ....

I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.

This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).

The question is how do I PIVOT and preserve the percentage of ownership?

View 3 Replies View Related

Transact SQL :: How To Convert Table Columns Into Rows In 2005

Aug 27, 2015

i have following table columns and i want to convert these all columns into row

SELECT [CASHINHAND]
,[CASHATBANK]
,[DEPOSITS]
,[ACCRECEVABLE]
,[SUNDRYDEBTORS]
,[LOANANDADVANCES]

[Code] ....

required output looks like

CASHINHAND 118950
CASHATBANK  200
DEPOSITS 3000
ACCRECEIVABLE
25000

View 3 Replies View Related

Transact SQL :: Convert Unknown Number Of Questions From Rows Into Columns

Jun 17, 2015

Using the following tables and data---

CREATE TABLE tblRiskReviewHistory(RiskReviewID int, RiskReviewHistoryID int, Name nvarchar(20), Description nvarchar(50), Date date)
INSERT tblRiskReviewHistory(RiskReviewID, RiskReviewHistoryID, Name, Description, Date)
VALUES(1,1,'Customer A','Profile Assessment','01/01/2015'),
(1,2,'Customer B','Profile Assessment','02/20/2015')

[Code] ...

And currently outputs;

Name Description Date Question Answer
Customer A Profile Assessment 01/01/2015

How complex is the structure?

Customer A
Profile Assessment
01/01/2015
The total value of assets?
Less than GBP 1 million

Customer A
Profile Assessment
01/01/2015
The volume of transactions undertaken?
Low (-1 pmth)

[Code] ....

However, I would like it to output;

Name
Description
Date
How complex is the structure?
The total value of assets?
The volume of transactions undertaken?
How was the client introduced?
Where does the Customer reside?

[Code] ....

The number of questions are unknown for each RiskReviewID and they can be added to in the future.

View 7 Replies View Related

Convert The Rows Into Column

Dec 11, 2007

I want to convert the row value as column name
example:-->
value       ratio
3           4.166666666661          1.315789473680          00          0
To :->
value     ratio        ratio1   ratio2   ratio3
3          4.166      1.315    0          0
any ideas?
 
 

View 1 Replies View Related

Convert Rows To Column

May 27, 2008

Hi all,

I have a table in sql server in this format.

ID Color Density Value

1 W 1.5 100
2 X 1.5 100
3 Y 1.5 100
4 Z 1.5 100
5 W 1.6 98
6 X 1.6 98
7 Y 1.6 98
8 Z 1.6 98.1


Now I want to display the data in this format

ID Color Density Value ID Color Density Value ID Color Density Value ID Color Density Value
1 W 1.5 100 2 X 1.5 100 3 Y 1.5 100 4 Z 1.5 100
5 W 1.6 98 6 X 1.6 98 7 Y 1.6 98 8 Z 1.6 98

Basically I want this format so that I can bind this to the datagridview directly and then use the same for a two way binding (saving) back to the database.
How do I get this result format using a query? Thanks in advance.

View 18 Replies View Related

Transact SQL :: To Convert Datatype For A Column

May 29, 2015

I am using SQL server 2008 .I have a table which has 1.5 crore records and some of my columns are Char datatype.Because of this i have spaces in my columns.Now I want to convert char to varchar datatype .But i have index on those columns .

View 2 Replies View Related

Transact SQL :: How To Convert Row To Column In Server

May 11, 2015

I have a store procedure , a want to convert table result from row to column .My store

CREATE PROC GET_FUNCTION @GROUP_ID CHAR(3)
AS
SELECT A.GROUP_MOD_ID, B.FUNCTION_MOD_NAME,A.ALLOW FROM
FUNCTION_GROUP A INNER JOIN FUNCTION_MOD B ON A.FUNCTION_MOD_ID=B.FUNCTION_MOD_ID
WHERE A.GROUP_MOD_ID=@GROUP_ID

And this is my result

Ok , i want to convert same. NOTE : Number of row is can more , G02   Add-Delete    1 , G02    Add-Edit    1 , ..etc. . I want it dynamic column follow row

GROUP_MOD_ID       Add      Edit      Delete       ABC       XYZ     etc....

G02                          1             1            0            0             1      

View 9 Replies View Related

Transact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?

May 1, 2015

I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1.  To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique. 

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.

Result:

PID1 

View 2 Replies View Related

SQL Query To Convert Rows Into Column

Jan 16, 2008

I have detail table like

date item_id grade in out
------ ------- ------- ----- -----
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8


Result View
=======

Item_id A B O T Total
------- --- ---- --- --- -------
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 -8 20 -10 2

I want result group by Item_id and sum of grade in column .
where grade could be any Alphabet, the column of result query could varies.
Each grade contains sum of (in-out) of item_id of detail table.

Smartsys99

View 1 Replies View Related

Transact SQL :: Convert Varchar (max) Column Data

May 20, 2015

I have a column in table which stores the actual signature from the singature pad. In the table it is stored as varchar(max) datatype and sample data looks like below. Below data is the actual signature of the person.

CREATE
TABLE #test
([Signature]
VARCHAR(MAX))

[code]...

How do i decrypt this data so i can print actual signature on my report.

View 2 Replies View Related

T-SQL (SS2K8) :: Convert Non-delimited Column Value Into Rows

Nov 14, 2014

I have a scenario wherein one of the column values in a row contains a string value which is non-delimited (as shown below). I need to split them by 2 characters and generate as many rows as count of set of 2 digits in that string.

Current record

RecordID Name CountyList
1 ABC 00
2 CDE 01020304
3 XYZ NULL

Expected output:

RecordID Name CountyList
1 ABC 00
2 CDE 01
2 CDE 02
2 CDE 03
2 CDE 04
3 XYZ NULL

I already have a solution in place to run it thru cursor and then do a while loop on the CountyList column by taking 2 digit value using Substring function (keeping start position dyanamic and jumping 2 positions).

View 2 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows In Single Column

Sep 4, 2014

Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.

Below is sample of data:

create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')

Required output

depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2

View 2 Replies View Related

Reporting Services :: Convert Column To Rows In SSRS

Jul 27, 2015

I am having table like below:-

ManagerName, ManagerEmail, Client, P1-Client  ,P1-Number,P1-Description,P2-Client,P2-Number,P1-Description,P3-Client,P3-Number,P3-Description

But I need report below :

ManagerName, ManagerEmail, Client , P1   P1-Client 
                                                                   
P1-Number
                                                                    
P1-Description
                                                            
P2  
                                                                    
P2 -Client 
                                                                   
P2-Number
                                                                    
P2-Description

View 3 Replies View Related

Transact SQL :: How To Insert Dynamic Column Values Of A Table To Variables

Jul 18, 2015

I am trying to insert different number of columns into variables.  This is what it does If I use a static columns.

declare @AccountType nvarchar(10)
declare @Total numerical(15,2)
declare @1 numerical (15,2)
declare @2 numerical (15,2)
declare @3 numerical (15,2)

#MonthtoDate  temp table is created using a dynamic pivot query. 

Data looks like this :

Account Type  1 2
3 Total
Type 1 3
0 4 7
Type 2 5
7 1 13

Select @AccountType = AcctType , @Total = MonthToDate, @1 = [1], @2 = [2], @3 = [3]  from #MonthtoDate 

However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .

We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .

View 6 Replies View Related

Transact SQL :: Based On One Column Need To Cascade Rows One By One

Sep 23, 2015

Here's the my structure and data as follows

>
SELECT tranno ,mrno medrecno ,createdon,createdat,no_of_trans nooftrans 
FROM mytab WHERE mrno = 'MR1514' and tranno = 1111
ORDER BY no_of_trans
tranno medrecno
createdon createdat
nooftrans

[Code] ...

Now requirement is : -

tranno medrecno
createdon createdat
nooftrans     tranno medrecno
createdon createdat
nooftrans

[Code] ....

View 4 Replies View Related

Transact SQL :: How To Repeat Rows Based On Column Value

Apr 30, 2015

I have a query that gives me a result with a column value for example 4.

I now want to repeat this row 4 times with a new column that calculated from 1 - 4.

Or when column value is 3 I want to repeat row 3 times with new column name 1-3

View 7 Replies View Related

Transact SQL :: Getting Time Difference Between Same Column In Different Rows

Jun 21, 2015

I have a table data like below

id         type      timestamp
1001    start1    10:34:23:545
1001    start2    10:34:24:545
1001    end2     10:34:24:845
1001    end1     10:34:25:545
1002    start1    10:34:25:645
1002    start2    10:34:25:745
1002    end2     10:34:25:945
1002    end1     10:34:25:965

I need the result as follows

id              millisecond diff start1end1                 millisecond diff start2end2
1001    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp
1002    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp

SQL Server 2008 R2

View 5 Replies View Related

Transact SQL :: Combine Multiple Rows In A New Column

Jul 16, 2015

I have the table below and like to combine the rows to create a single link row in a new column. The rows should be combined based on the job number columns which is the same for the rows to be combined.

DECLARE @M31
( M31_SQL_ID INT
,JOB_NUMBER INT
,LINE_NUMBER INT
,WORKS_DESC VARCHAR)

[Code] ...

Output should be as below

219242
16/7/15 called tenant and she thought we would just fix for free - advised her I can get a quote how ever she may have to pay - she will call back  

219245
16/7/15 called tnt said no report number. Said she will speak with her husband and call back with her decision and 16/07/15 the work order was sent to agent ...

View 3 Replies View Related

Transact SQL :: Converting Rows To Column And Getting Data

Jul 22, 2015

I have a sql table like this

event_id timestamp event_name event_score
1 3000 alarm 0.95
10 3500 alarm 0.85
5 4000 alarm 0.93
20 4200 alarm 0.87
30 5000 alarm 0.87
30 8000 alarm 0.97
40     9000        alarm       0.98 
13        9700        alarm       0.98

And I am expecting output like this(With multiple rows)

event_id1 event_id2 event_id3 event_name event_score1 event_score2 event_score3
1 10 5 alarm 0.95 0.85 0.93
30 40 13 alarm 0.97 0.98 0.98

Also I have certain conditions like the timestamp difference between event_Id1 and event_Id3 < 3600 eg 1 hour

View 6 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Transact SQL :: Convert Non Fixed Rows To Non Fixed Columns Dynamically?

Oct 5, 2015

I have a table with 3 columns  (ID Int , Name Varchar(25), Course Varchar(20))

My source data looks like below

ID      Name        Course
1        A                Java
1        A                C++
2        B                Java
2        B                SQL Server
2        B                .Net
2        B                 SAP
3        C                 Oracle

My Output should look like below...

ID      Name       Course(1)     Course(2)         Course(3)     Course(4)  

1        A                 Java            C++
2        B                 Java            SQL Server .Net             SAP
3        C                 Oracle

Basically need t-sql to Convert non fixed rows to non fixed columns...

Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)

Create SQL:

Create table Sample (ID Int null , Name  Varchar(25) null, Course Varchar(20) null)

Insert SQL:

INSERT Sample (ID, Name, Course)
          VALUES (1,'A','Java'),
                 (1,'A','C++'),
                 (2,'B','Java'),
                 (2,'B','SQL Server'),
                 (2,'B','.Net'),
                 (2,'B','SAP'),
                 (3,'C','Oracle')

View 12 Replies View Related

Transact SQL :: Get Only Newly Inserted Rows By Datetime Column?

Aug 17, 2015

I have a TableA where data get inserted from Excel(IMPORT/EXPORT wizard)

TableA;
ID(identity)   Date (NOT NULL Defaulyt Getdate() )                          
 Name            Phone
1                   2014-06-17 17:28:21.190          
Nick              12345678910
2                  2014-05-17 17:28:21.190        
 Stan              00045678910
3                  2015-08-17 17:28:21.190        
 Kim                 11111678910
4                  2015-08-17 17:28:21.190          
Tom                NUll 

 3,4 are the rows i have inserted now , you can see by date, likewise i have 100,000 rows(old and new combination) and now the data from excel to TableA can be imported/exported daily , hourly, weekly basis.

Now i want to find out only the rows which are imported to tableA today, or hours back, or yesterday .....

 the reason is , 

Step1:get data from excel and import to tableA( this is a manual Step) and i know when the data is inserted  with exact date and time.

Step2: get newly inserted rows from TableA and pass them as Parameters in Stored Procedure.( i may run step 2 after 1 hour, or after  1 day or after 1 week ,but i want only rows that are inserted ) 

I tried with using where Datecreated, but did work.

View 4 Replies View Related

Transact SQL :: Keep Entire Columns Into 2 Rows When There Is A Comma In A Column

Nov 2, 2015

I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.

ID       Name                  State      Country
1       Scott, Ricky            NSW       AUS
2       Martin                   VIC         AUS
3    James, Peter,John     WA        AUS

ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below

ID       Name                  State      Country
1       Scott                     NSW       AUS
1       Ricky                     NSW       AUS
2       Martin                   VIC         AUS
3      James                     WA        AUS
3      Peter                      WA        AUS
3      John                       WA        AUS

How to strip the camma(,) in SQL?

View 3 Replies View Related

Transact SQL :: How To Generate Rows Certain Times With A Counter Column

Jul 10, 2015

How can I write it in SQL? I have a table  that column  A   has two values of   Product  and Cost...Where value of A is Product, I have to add 6 more rows, and add column b with values from 1 to 7 for each rows ( counter)Where value of A is Cost, I have to add 4  more rows, and add column b with values from 1 to 5 for each rows ( counter).

View 3 Replies View Related

Transact SQL :: Need To Convert Multiples Rows Data To 1 Row Data

Nov 13, 2015

I need to create SQL to convert multiple rows data to single row for given subscriber#. Below is the example. In below example , I've 4 family members with same subscriber # and each members have separate rows, I want to combine member data for same subscriber in 1 row, so there would be a 1 row for each subscriber. 

View 6 Replies View Related

Transact SQL :: Split Column To Rows Which Is Delimited Using Commas And Semicolons

Jul 9, 2015

All I have a situation where I need to split the column to rows which is delimited using commas and semicolons. Please find the below sample data.

Data in Tables

Test1, Test2, Test3
Test4;Test5;Test6

Desired output
Test1
Test2
Test3
Test4
Test5
Test6

Is there any way that I can get this output in SQL other than using the XML Conversion, since the data has some special characters in this.

View 7 Replies View Related

Transact SQL :: Arithmetic Calculation Between Two Rows And Result In Alias Column?

Jul 12, 2015

I am trying to compare Sales value of year 2015 with sales value of 2016 and the difference stored in alias column as Sales_growth for year 2016 , for year 2015 the alias column be as '1' similarly difference between margin of 2015 and 2016 and result stored in alias column as margin_rate in year 2016for 2015 as 1 but when there is no record for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100 

Formula for calculation

SGR = (sales(2015)-sales (2016)) / Sales_growth(2015)
SGR = (3456.05-3603.33) /3456.05 = -0.043
MR =( margin (2015)-margin( 2016) / margin(2015)
MR = (1059.24-1053.07)/1059.24= 0.006
DECLARE @T1 TABLE

[code]....

last record : as there is no record  for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100

View 18 Replies View Related

Transact SQL :: Strategy To Translate Column Data Into Distinct Rows

Aug 27, 2015

I am writing a query where I am identifying different scenarios where data changes between one week and the next. I've set up my result set in the following manner:

PrimaryID       SKUChange              DateChange         LocationIdChange        StateChange
10003             TRUE                       FALSE                  TRUE                          FALSE
etc...

The output I'd like to see would be like this:

PrimaryID        Field Changed          Previous Value      New Value
10003             SKUName                 SKU12345           SKU56789
10003             LocationId                 Den123               NYC987
etc...

The key here being that in the initial resultset ID 10003 is represented by one row but indicates two changes, and in the final output those two changes are being represented by two distinct rows. Obviously, I will bring in the previous and new values from a source.

View 3 Replies View Related

Transact SQL :: Check 2 Columns And Return Rows As Long As One Column Is Not Null

Oct 19, 2015

how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.

Example Data

create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),

[code]....

View 4 Replies View Related







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