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