|
Hi All,
I am using the following script to transpose a particular column row as columns, but the problem is I am able to get only one row, for example I have StudentNames, Ids, AggregateMarks, and TotalMarks.
If I use the below approach and generate a column for every student then I am able to get only TotalMarks for each student, but I want to get Ids, AggregateMark also under each studentName
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from yourtable
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
Can anybody please help me in this, any code snippet or suggestion or a link would be very very helpful, please thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You could have a look at this[^]
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
I have Printed the dynamic query, it is as below. But same thing how to get that ServiceLevel from the below query, it is printing only Service id for each column in a row, but I want Service Level also in another row, can't we do it? Any sort of help would be very very helpful, please help me I am also trying my best. Thanks in advance.
SELECT [Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],[Knee Care],
[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair] from
(
select ServiceLevel, ServiceId, Name
from [Service]
) x
pivot
(
max(ServiceId)
for Name in ([Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],
[Knee Care],[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair])
)
p
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
@indian1433, here is a way (The first version is static)
DECLARE @T TABLE(StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO @T VALUES
('NAME1',1,300,503),
('NAME2',2,250,500),
('NAME3',3,378,504),
('NAME4',4,490,500),
('NAME5',5,399,500)
SELECT X.ID,X.TotalMarks,t1.[AggregateMarks],X.[NAME1],X.[NAME2],X.[NAME3],X.[NAME4],X.[NAME5]
FROM @T
PIVOT
(
MAX(AggregateMarks)
FOR StudentNames
IN ([NAME1],[NAME2],[NAME3],[NAME4],[NAME5])
) AS X
JOIN @T t1 on t1.Id = X.Id
ORDER BY 1
I have done this one to show you how it must go like. The dynamic version follows
CREATE TABLE #T (StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO #T VALUES
('NAME1',1,300,503),
('NAME2',2,250,500),
('NAME3',3,378,504),
('NAME4',4,490,500),
('NAME5',5,399,500)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from #T
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT X.ID,X.TotalMarks,t1.AggregateMarks, ' + @cols + N' from #T
PIVOT
(
MAX(AggregateMarks)
FOR StudentNames IN (' + @cols + N')
) AS X
JOIN #T t1 on t1.Id = X.Id
ORDER BY 1'
print @query
exec sp_executesql @query
DROP TABLE #T
Hope this helps.
modified 31-Oct-15 2:14am.
|
|
|
|
|
|
Linking to an article on your own blog, without posting the relevant details here, is considered spam.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Okies... Thanks for the information...Will take care...
|
|
|
|
|
Hi,
I am wondering why I am getting error when running below simple IF in MySQL?
IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN
BEGIN
INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time) VALUES (10, '2015-10-11 13:14:16');
END;
END IF;
This is the error I am getting:
[SQL] IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN
BEGIN
INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time) VALUES (10, '2015-10-11 13:14:16');
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE empl' at line 1
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
..probably because you are showing a query for SQL Server. The error says to look up the correct syntax[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
Hi All,
We are moving the data from a huge database into another database as we guess we have Duplicate values on Name and Description fields in some tables we want to list our all those Tables which have these columns and duplicates on those columns using some script dynamically.
Is there any way to write that sort of script if some body already did it and can help me, it would be a great help I am also searching any link, suggestion or code snippet would be a great help.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
How to do it would depend a lot on the database. You should update your post with that info.
|
|
|
|
|
anyways is fine it is just an internal running script but I want to run it on all tables in the selected database on selected server. I though of little bit with dynamic sql but yes that's question too, is there any better approach for it than dynamic sql?
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
@indian1433, you can try with sp_MSforeachdb and sp_MSforeachtable .Please check out and let us know if the hint has helped you.
N.B.~ Since you mentioned that it's just for internal purpose, so I have mentioned those two undocumented Stored Procedure. Please don't use it in production since they r undocumented and there is no guarantee that in future MS will not remove them (:
|
|
|
|
|
Hi All,
I have an Excel file in which there are multiple sheets and its columns are spread in multiple rows like a parent has multiple children and those children have their children.
How can I load this data in to a table sheet by sheet, means for each load if it loads from one sheet that's fine but important thing here is columns are spread into multiple rows.
I am also searching, but any link, any suggestion or code snippet is going to be very very helpful. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
Sorry I didn't mention it may be but I want to do it SQL Server script. So I am trying in the following way
SELECT * INTO #Servxxx
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;
Database=D:\Servxxx.xls',
[Servxxx First Sheet$]);
It is giving me the following error
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'D:\Servxxx.xls'. Make sure the object exists and that you spell its name and the path name correctly. If 'D:\Servxxx.xls' is not a local object, check your network connection or contact the server administrator.".
Msg 7303, Level 16, State 1, Line 10
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
It is saying file is not there, but file is there and it is erroring out with link server etc.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Is D: a local drive, or a mapped network drive?
Does the SQL service account have permission to access the file?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hey Rick, thanks I got it sort out my friend here is the sample one may be somebody has the same issue.
SELECT * INTO #xxxxxxs
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;
Database=C:\xxxxxxx\Dont Remove ThisFolder\xxxxxxs.xlsx',
'SELECT * FROM [Back xxxxx$]');
I have a question here, do we have any way to copy the existing schema structure into a table variable, along with an identity column?
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
I am using Sql server 2008.
I have 3 columns say, UserID,ManagerID,deptCode.
I need the output as UserID,ManagerID,deptCode,ManagersDeptCode
So, the column "ManagerID" need to be used to generate the output of "ManagersDeptCode".
Please help.
|
|
|
|
|
Join back to your user table using the managerid ot userid to get the managers details
Select U., M.
From UserTable U
inner join UserTable M on M.userID = U.ManagerID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I have a table A in it I have columns like Id, ParentId, ParentName, Child1Id, Child1Name, Child2Id, Child2Name. ParentId is the parent of Child1Id, and Child1Id is the parent of Chil2Id. Id is the Primary key of this table.
I want to have a select statement which, I have to get ParentIds, ParentNames order by ParentNames, under each Parent, I want to have Child1Ids, Child1Names, order by Child1 Names, Under each Child1s, I want to have its child2Ids and Child2Names.
ParentId, ParentName
Child1Id, Child1Name
Child2Id Child2Name
Child2Id Child2Name
Child2Id Child2Name
Child1Id Chil1Name
Child2Id Child2Name
Is there any way to write a query like this? Please help me, any type of help, a link, a code snippet or a suggestion would be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi everyone, I have a temporary table as shown below
tempTable(ID int not null identity(1,1) primary Key, Col1 int, Col2 int, Col3 int)
I am trying to get data of each column, store them in a variable, and manipulate them as I loop through the table using while loop.
The following is what I have tried
create proc [dbo].[spGetData]
@Col1 int
@Col2 int
@Col3 int
as begin
declare @count int
declare @currentColumn varchar(25)
declare @currentColumnVal varchar(25)
set @count = 1
set @currentColumn = NULL
set @currentColumnVal = NULL
create #tempTable(ID int not null identity(1,1) primary Key, Val1 int, Val2 int, Val3 int)
insert #tempTable(Val1, Val2, Val3) Values(@Col1, @Col2, @Col3)
set @count = 1
while @count < 3
begin
set @currentColumn = 'Val' + CAST(@count AS VARCHAR(25))
select @currentColumnVal = @currentColumn from #tempTable
set @count = @count + 1
end
print @currentColumnVal
end
I'm not getting any errors but I'm getting 0 as the return value which is incorrect.
modified 23-Oct-15 20:46pm.
|
|
|
|
|
What were you expecting? There is no final select in this SP. In fact you can't create the SP because there are errors reported.
If I fix the errors ... commas after @Col1 int and @Col2 int and create TABLE #tempTable the Stored Procedure is created and when I run it, it prints "Val2" - which is what I would expect given what you have in the body of the SP.
Are you actually trying to use dynamic sql? Have a look at this CP article - Building Dynamic SQL In a Stored Procedure[^]
You also need to understand how to return values from Stored Procedures - see MSDN article[^]
|
|
|
|
|
--[Spgetdata] 1,2,3
ALTER PROC [dbo].[Spgetdata] @Col1 INT,
@Col2 INT,
@Col3 INT
AS
BEGIN
DECLARE @count INT
DECLARE @currentColumn NVARCHAR(25)
DECLARE @currentColumnVal NVARCHAR(25)
SET @count = 1
SET @currentColumn = NULL
SET @currentColumnVal = NULL
CREATE TABLE #tempTable
(
ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
Val1 INT,
Val2 INT,
Val3 INT
)
INSERT #tempTable
(Val1,
Val2,
Val3)
VALUES(@Col1,
@Col2,
@Col3)
SET @count = 1
WHILE @count <= 3
BEGIN
SET @currentColumn = 'Val' + Cast(@count AS VARCHAR(25))
--SELECT @currentColumnVal = @currentColumn
--FROM #tempTable
DECLARE @Q NVARCHAR(Max)
SET @Q = 'SELECT @currentColumnValX = '
+ @currentColumn + ' FROM #tempTable'
EXEC Sp_executesql
@Q,
N'@currentColumnValX varchar(500) Output',
@currentColumnVal Output
-- Do stuff
-- Do more Stuff
SET @count = @count + 1
END
PRINT @currentColumnVal -- Just to see what the value of the last Column is
END
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
