|
I have a table with the following schema:
DECLARE @images TABLE
(
[Id] BIGINT NOT NULL,
[UserId] NVARCHAR(450) NOT NULL,
[VehID] BIGINT NOT NULL,
[VehImage] VARBINARY(MAX) NULL
);There are no exactly 10 rows per user. Currently, all VehImage columns are null (intentionally).
Here is my sql query (@images is a table variable that I pre-populate with the desired records):
SELECT UserId,VehID
,[Image1],[Image2],[Image3],[Image4],[Image5]
,[Image6],[Image7],[Image8],[Image9],[Image10]
FROM (
SELECT UserId
,VehID
,VehImage
FROM @images
) AS SourceTable
PIVOT (
Count(VehImage)
FOR VehImage IN ([Image1],[Image2],[Image3]
,[Image4],[Image5],[Image6]
,[Image7],[Image8],[Image9]
,[Image10])
) AS PivotTable;I have the pivot working, in that I get exactly one row back with the expected UserId, and VehId, and 10 image columns. My problem is that the Image columns all have the value 0 instead of the expected null.
What am I doing that would cause that?
EDIT ----------------------------------------
I changed the aggregate function from count to max, and all of the images are now showing null as expected. I don't know yet if that's the ultimate solution.
EDIT #2 -------------------------------------
Nope, I plugged a fake value into the first image record, and all of the image columns are still coming back as null...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 19-Jun-22 8:13am.
|
|
|
|
|
#realJSOP wrote: Currently, all VehImage columns are null (intentionally).
...
FOR VehImage IN (...)
If all of the VehImage values are Null, they won't match the IN filter.
And it seems odd to be pivoting on the same column you're aggregating.
What are you actually trying to do?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
i want to return a row that has all of the users images in it.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Maybe something like this?
WITH cte As
(
SELECT
UserId,
VehId,
VehImage,
ROW_NUMBER() OVER (PARTITION BY UserId, VehId ORDER BY Id) As RN
FROM
@images
)
SELECT
UserId,
VehId,
[1] As Image1,
[2] As Image2,
[3] As Image3,
[4] As Image4,
[5] As Image5,
[6] As Image6,
[7] As Image7,
[8] As Image8,
[9] As Image9,
[10] As Image10
FROM
cte As SourceTable
PIVOT
(
Max(VehImage)
FOR RN IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
) As PivotTable
; Or without the pivot:
WITH cte As
(
SELECT
UserId,
VehId,
VehImage,
ROW_NUMBER() OVER (PARTITION BY UserId, VehId ORDER BY Id) As RN
FROM
@images
)
SELECT
UserId,
VehId,
MAX(CASE RN WHEN 1 THEN VehImage END) As Image1,
MAX(CASE RN WHEN 2 THEN VehImage END) As Image2,
MAX(CASE RN WHEN 3 THEN VehImage END) As Image3,
MAX(CASE RN WHEN 4 THEN VehImage END) As Image4,
MAX(CASE RN WHEN 5 THEN VehImage END) As Image5,
MAX(CASE RN WHEN 6 THEN VehImage END) As Image6,
MAX(CASE RN WHEN 7 THEN VehImage END) As Image7,
MAX(CASE RN WHEN 8 THEN VehImage END) As Image8,
MAX(CASE RN WHEN 9 THEN VehImage END) As Image9,
MAX(CASE RN WHEN 10 THEN VehImage END) As Image10
FROM
cte As SourceTable
GROUP BY
UserId,
VehId
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
They both worked great, but I went with the non-pivot version. Thanks, man.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
I am doing a Result Analysis project for my college. So basically students will enter the grades that they have obtained for various subjects. If they fail for an exam and take the exam again they need to enter the details of that as well.
For the result, we have to find the list of students who passed for a subject in the first attemt, second attempt upto the nth attempt...What will be an efficient Database design for this problem? What tables should be created and what data should be stored in each?
Please help...struggling with this for the past few weeks
|
|
|
|
|
Consider taking some graph paper and drawing out what your student grade records will look like. Use that as a starting point to design how you will set up one or more tables in a database.
|
|
|
|
|
|
Thank you to those who gave suggestions!! I have successfully solved the problem and completed my project!
|
|
|
|
|
I don't know how to write this PL/SQL in oracle, thanks for your advice.
Because the amount of data in the table is large, it is not suitable to find the max PDate and then compare it from the source table.
Table data:
Process PDate Status
100 2022/05/21 19:58:03 0
100 2022/05/22 07:01:00 0
100 2022/05/22 13:01:10 1
101 2022/05/22 08:23:39 0
101 2022/05/22 10:17:39 0
101 2022/05/22 17:49:39 1
102 2022/05/22 13:14:48 0
Requirement: Get the status of the last record of different processes on 2022/05/22
Select result:
Process PDate Status
100 2022/05/22 13:01:10 1
101 2022/05/22 17:49:39 1
102 2022/05/22 13:14:48 0
|
|
|
|
|
First filter by PDate = datevariable, order by PDate Descending. Select the top 1 record.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hello everybody,
It is true that is not a good idea use store procedure actually?
I mean, a) Not a good idea use it in sql
and b) Using o call store procedure in a programming language?
it is so: What is the best way?
|
|
|
|
|
No. Using stored procedures is perfectly fine, and is often preferable to embedding queries directly in your application code.
Whoever told you it's not a good idea clearly doesn't know what they're talking about.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This is something I found some of the younger generation like to promote, put all your logic and processing in the business layer, make the database purely a storage facility. The argument is that you can spawn more processes from the BL to support growth.
IMHO this is utter and absolute bullshit! Databases are optomised to support data PROCESSING as well as storage, that processing is done by stored procedures.
We once did a test, I wrote a SP and a rather brilliant junior wrote a C# process to do the same long running process, the DB was dramatically faster.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Mycroft Holmes wrote: the younger generation like to promote
Where that typically means one or more of the following.
1. They are inexperienced.
2. They have no idea how to solve problems that show up
3. They have no expectation that problems will even show up.
4. They have no intention of being the one tasked with fixing the problems.
|
|
|
|
|
I have a MySQL table with start and end dates.
E.g rows like this:
start | end
2022-03-01 | 2022-04-10
2021-12-01 | 2022-03-11
2022-01-01 | 2022-04-05
...
I want to count how many of the days in those ranges (all rows) were in April 2022 for instance.
The first record has 10 days in April, the third record has 5 days in April, so the result should be 15.
|
|
|
|
|
So what ideas have you considered so far?
|
|
|
|
|
As you have not shared your attempt at solving this for yourself, nor responded to Craig, I am only going to give you hints to a possible solution. For my test data I used this
declare @demo table (startdate date, enddate date)
insert into @demo (startdate, enddate) values
('2022-03-01', '2022-04-10'),
('2021-12-01', '2022-03-11'),
('2022-01-01', '2022-04-05');I also hard-coded the start and end dates for the month I am interested in
declare @1stDay date = '2022-04-01';
declare @LastDay date = '2022-04-30'; I then wrote some sql that would convert the dates I had on the table to only those that fell into the month I am looking at. E.g. Something like this
select startdate, enddate
,case when startdate < @1stDay then @1stDay
when startdate > @lastDay then @LastDay
else startdate end as AmendedStartDate
,case when enddate < @1stDay then @1stDay
when enddate > @LastDay then @LastDay
else enddate end as AmendedEndDate
from @demo; I then used those Amended dates in a datediff calculation to get the number of days in each range that fell in April 2022.
This approach is flawed - in that I get 9, 0 and 4 as the results instead of 10, 0, 5. I will leave that as an exercise for you to sort out. I also coded this in MSSQL so you may need to make minor syntactical changes
|
|
|
|
|
Given:
1) Developing a WebService on a DMZ server which will connect back to corporate SQL2019 server.
2) There are lots of other databases on this server, however there will be only one database (DB_X) which these WebServices need to interact.
3) Assume user, WEBAPI_USER, is created.
Also executed: DENY VIEW ANY DATABASE TO WEBAPI_USER;
4) Now, that user cannot see any databases via MS SQL Server Studio, not even the DB_X database which has been granted access
Note: Kind of crazy because if you run the query: SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U', you will get a full list of all the tables. If done some research on this and it appears that WEBAPI_USER would need to become the owner of the databaase to see it in MSSS.
Question: What is everyone else doing to create a limited access MS-SQL user ?
Thank you in advance.
David
|
|
|
|
|
Your description seems to be going backwards.
What you should be doing.
First create the user.
Second give it access to the specific database.
Until you give it access it should not be able to do anything. Which is why your description seems off. Seems to suggest that you created it with access to everything and now you want to restrict that access. So whatever you did to create it in the first place is wrong.
|
|
|
|
|
My database tbl_user has the following columns :
| User_id | Upline | Right_id | Left_id |
+--------+---------+----------+---------+
| 1 | 0 | 4 | 7 |
| 2 | 0 | 6 | 5 |
| 3 | 0 | 0 | 0 |
| 4 | 1 | 0 | 0 |
| 5 | 2 | 0 | 0 |
| 6 | 2 | 0 | 0 |
| 7 | 1 | 0 | 0 |
| 8 | 0 | 0 | 0 |
| 9 | 0 | 0 | 0 |
| 10 | 0 | 0 | 0 |
+--------+---------+----------+---------+
$user_id = 2;
$user_tree = $this->getAllRec("u.user_id",
"tbl_user u",
"WHERE u.Upline = '".$user_id."'
ORDER BY Right_id ASC");
print_r($user_tree);
I want the array `$user_tree` to hold (user_id 5) first, then (user_id 6), how to do that?
I want to print **Left_id** first, then only print **Right_id**. May I know how to do it?
Do I need to use left join or use another query?
modified 3-Apr-22 22:30pm.
|
|
|
|
|
|
Firstly - take note of the previous comment about SQL Injection!
Quote: I want the array `$user_tree` to hold (user_id 5) first, then (user_id 6), how to do that?
Then you need to order your results by [user_id] e.g.
select *
from @demo
where upline = 2
order by [User_id]; Quote: I want to print **Left_id** first, then only print **Right_id**. May I know how to do it?
I'm not entirely sure what you mean here, but it sounds like you want the columns in a different order in your result. In the example above I used select * - that is not actually good practice. It is much better to list the columns that you want, in the order that you want them - this helps protect any code that is using the results from any subsequent changes to the table schema, such as adding a new column.
However, if you mean you want to sort by one column within the "grouping" of another column then you simply add a list of things to sort by.
This example covers both the scenarios above
select Left_id,Right_id
from @demo
order by Upline, [User_id]; If you meant something else then reply to this solution and I will try to help
|
|
|
|
|
I'm writing a function which will join two collections and two filters for each.
public class Teacher
{
public string Id{ get; set; }
public string Name{ get; set; }
public string TeacherFilter{ get; set; }
...
}
public class Student
{
public string Id{ get; set; }
public string Name{ get; set; }
public string StudentFilter{ get; set; }
public string TeacherId{ get; set; }
...
}
public class TeacherStudents
{
public string Id{ get; set; } -- from teacher
public string Name{ get; set; } -- from teacher
public IEnumerable<Student> Students{get;set;}
}
public class TeacherStudents
{
public string Id{ get; set; } -- from teacher
public string Name{ get; set; } -- from teacher
public IEnumerable<Student> Student{get;set;}
}
public class TeacherStudent
{
public string Id{ get; set; } -- from teacher
public string Name{ get; set; } -- from teacher
...
public Student Student{get;set;}
}
Get(string TeacherFilter,string StudentFilter).
Here is how i implement it
var query = teacherCollection.Aggregate().Match(x => x.TeacherFilter== TeacherFilter);
var query1 = query
.Lookup<Teacher, Student, TeacherStudent>(studentCollection, t => t.Id, s => s.TeacherId, l => l.Student)
.Unwind(x => x.Student, new AggregateUnwindOptions<TeacherStudent>())
.Match(x => x.StudentFilter== StudentFilter)
For the teacher collection, useless data has been filter out which will reduce the data size when doing the lookup(join). But for the student collection, the match stage is appended after the lookup. so does all the data from student will join with teacher behind the scenes? is it possible to filter out some student data before the lookup?
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
