|
Anyone can please help me to understand pivoting in SQL
|
|
|
|
|
|
We have a couple of tables that we have to un-pivot - one has 245+ columns and (on average) about 510k rows - it needs to be un-pivotted down to 8 columns, and the columns we need to pivot are a mix of int, float, and varchar. This means we have to cast all of the pivoted columns to varchar, and then re-cast in the unpivot clause.
When we redesign the database (soon I hope), we won't have to un-pivot, but will instead have to pivot the columns into a schema the app can understand. Hopefully though, we won't have to do any un-pivoting/pivoting at all - it's a pretty expensive operation.
".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 27-Nov-19 11:20am.
|
|
|
|
|
Hello, all!
I'm experienced front-end developer. Few months ago I started play with back-end technologies - nodejs, express, mongo,mongoose. I can get everything quickly. Just need some help to initiate thinking over there.
Now I develop polls application and struggle with how to structure my mongoose schemas to implement my features. Also there is implemented simple authentication.
Don't worry everything will be explained step by step.
Features:
- User can create poll on "POST /polls". Poll contains question:string, and options:[string];
- User can vote for poll on "POST /polls/:pollID/vote" as select option index;
- User can get polls on "GET /polls?...". But you receive their vote result only if you are creator or voter. And you can see always the count of all voters;
- User can bookmark poll on "POST /polls/:pollID/bookmark;
- User can get all bookmarked polls on "GET /poll/:pollID/bookmarks sorted by bookmark date;
Front-end poll object looks like;
-Always show question, options, votesCount;
-Only show vote if user is creator or voted for the poll;
-Always show if user bookmarked the poll;
My current Poll schema:
{
question: {
type: mongoose.Schema.Types.String,
required: true,
},
options: {
type: [{
type: mongoose.Schema.Types.String,
required: true,
}]
},
optionsVote: {
type: mongoose.Schema.Types.Map,
of: mongoose.Schema.Types.Number,
},
createdAt: {
type: mongoose.Schema.Types.Date,
default: Date.now,
},
votes: {
type: [{
user: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User',
},
option: mongoose.Schema.Types.Number,
}]
},
votesCount: {
type: mongoose.Schema.Types.Number,
default: 0,
},
creator: {
type: mongoose.Schema.Types.ObjectId,
required: true,
},
bookmarks: {
type: [{
user: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User',
},
createdAt: mongoose.Schema.Types.Number,
}]
},
}
What I did then ...
{
...
voters: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'User',
}],
...
}
const polls = await Poll.aggregate([
{
$project: {
question: true,
options: true,
createdAt: true,
votesCount: true,
optionsVote: {
$cond: {
if: {$in: [mongoose.Types.ObjectId(req.user.id), "$voters"]},
then: "$optionsVote",
else: "$$REMOVE",
},
},
},
}
]).sort({[category]: order})
So my question ...
Can be divided into:
Is the current schema right for that features?
Should I create Bookmark and Vote collections?
What queries should I implement for bookmarks and votes to be with partially restricted access ?
Can you give me direction?
I will discuss, support and edit what is needed.
... And I am extremely grateful for your attention! Thank you very much! 
|
|
|
|
|
create ER diagram to represent the tables of the StayHome video rental shop (as described in the Access 2000 manual
|
|
|
|
|
What have you tried?
Where are you stuck?
What help do you need?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
i want make for me ER diagram
|
|
|
|
|
Then you need to consider what is the best tool to help do whatever you want. No one here is going to do it for you.
|
|
|
|
|
Member 14635620 wrote: (as described in the Access 2000 manual
And have you read what was described in the Access 2000 manual?
|
|
|
|
|
Can you please do my homework for me based on a product manual that is 19 years old?
Who in Ghu's name is using the Access 2000 manual as a reference.
Presumably there is an teaching institution somewhere attempting to teach you.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Probably the same one that teaches VB6.
|
|
|
|
|
Sir i want to fetch data from a table where status in Debit and Credit.So i want amount saprate with debit and credit. and its diffrence.Please help me
|
|
|
|
|
There's no way anyone can help you based on that description.
You need to show us the structure of your table, the sample input, and the expected output.
You also need to show us what you've tried, and tell us where you're stuck.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
How can we help?
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
So I'm new to MongoDb; well I have about a year of experience, and I'm trying to write my first really complicated read.
What I'm trying to do, is grab a Brand by name, then all the products that match the brand name, then go through the products and get all the different distinct category names. Finally, grab the categories that match the list of category names.
So I'm just down to the final part, I have a list of distinct category names that are available by an enumerator, and I have a list of categories, I just can't figure out how to do the final part. I don't know what to call it either to search for help. I thought about a loop going through the categories, but I'm not sure how to unpack my distinct list of category names. Or perhaps the authors already thought of this and wrote something.
The categoryDistinct works.
categoriesSelected , my new list of categories
categories , a list of all the categories
var brandTask = _context.Brands.Find(b => b.Name == brandName).FirstOrDefaultAsync();
var productsFiltered = _context.Products.Find(b => b.Brand == brandName).ToList();
var categoriesDistinct = productsFiltered.GroupBy(c => c.Category).Select(x => x.FirstOrDefault());
var categoriesSelected = new List<Category>();
var categories = await context.Categories.Find( => true).ToListAsync();
var productFilter = Builders<Product>.Filter.Eq("Brand", brandName);
var productsTask = _context.Products.Find(productFilter).ToListAsync();
await Task.WhenAll(brandTask, productsTask);
return new GetBrandPage { Brand = brandTask.Result, Categories = categoriesSelected, Products = productsTask.Result };
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
I came up with this and it works so far. A combination of Mongo and Linq.
Changed some categoryDistinct to productDistinct , better name.
I actually didn't think it would work.
var brandTask = _context.Brands.Find(b => b.Name == brandName).FirstOrDefaultAsync();
var productsFiltered = _context.Products.Find(b => b.Brand == brandName).ToList();
var productsDistinct = productsFiltered.GroupBy(c => c.Category).Select(x => x.FirstOrDefault());
var categoriesSelected = new List<Category>();
foreach (var product in productsDistinct)
{
var cx = _context.Categories.Find(c => c.Name == product.Category).First();
if (cx != null)
{
categoriesSelected.Add(cx);
}
}
var productFilter = Builders<Product>.Filter.Eq("Brand", brandName);
var productsTask = _context.Products.Find(productFilter).ToListAsync();
await Task.WhenAll(brandTask, productsTask);
return new GetBrandPage { Brand = brandTask.Result, Categories = categoriesSelected, Products = productsTask.Result };
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Hi,
Working in a time tracking program, I've thought of separating results in pages.
I think of making a specific SQL query to count the amount of total registers with the applied filters, then call the another SQL query with LIMIT clause to be able to define from which record I want to start showing the results and the amount of results.
Is there any better way to do it?
Thank you in advance!
|
|
|
|
|
|
I have a column and i need to find in which table this column exists throughout whole DB Using query
|
|
|
|
|
The column name is not unique. The column with a particular name may be in every DB table.
And BTW, what DBMS are talking about: MS Access, SQL Server, Oracle, PostgrSQL, ...?
|
|
|
|
|
Assuming Sql Server:
SELECT SCHEMA_NAME(schema_id) [Schema Name]
,t.name AS [Table Name]
FROM sys.tables AS t
JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name = 'ColumnName'
|
|
|
|
|
How do i browse to let's say 10 records before the last one? Code below:
Dim con As String =
"User=SYSDBA;PASSWORD=masterkey;Database=/DATABASE/TIME_DBS/TC_SHPIRAG3ST4.gdb;Datasource=192.168.2.78;Port=3050;Dialect=3"
Dim conexiune As FbConnection = New FbConnection(con)
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
conexiune.Open()
Try
Dim sCmd As FbCommand
Dim sql As String
Dim ds As FbDataReader
sql = "select * from RT_TIME ORDER BY RTTI_COUNTER desc"
sCmd = New FbCommand()
sCmd.Connection = conexiune
sCmd.CommandText = sql
ds = sCmd.ExecuteReader
ds.Read()
TextBox1.Text = ds("RTTI_BIT_POS")
Label1.Text = ds("RTTI_HOOK_POS")
ds.Close()
conexiune.Close()
Catch ex As FirebirdSql.Data.FirebirdClient.FbException
MsgBox(ex.ToString, vbCritical, "DB Error")
End Try
End Sub
modified 22-Sep-19 23:38pm.
|
|
|
|
|
|
thanks, i didn't find the right information
|
|
|
|
|
nevermind, found it: sql = "select FIRST 1 SKIP 10 * from RT_TIME ORDER BY RTTI_COUNTER desc "
|
|
|
|
|