Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Windowing and Pivot

    Just curious more than anything. What approach would you use to move repeating columnar information into it's own column?

    Example - You have a database, schema, tables, columns, until now it's one to one, but when you start to capture indexes or constraints you can have multiple IX and Constraints per column. In the event you want to report on these in their own column what strategy would you use?

    I was thinking about row_number over rank or partition on table, then order by column first, then index. I am thinking this would give me unique grouping that would be required. Your row number / rank would be at the table level while your columns and indexes are order by. I'm thinking you could then somehow pivot the information (index) column into it's own column.

    Has anyone ever tried this type of approach? I'm pretty new to the wonderful world of SQL and visualizing the movement of data doesn't come all that easy for me.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your question isn't clear to me.
    Perhaps you can provide some example data before and after transformation?

    In terms of pivoting data: this is almost exclusively better to do in your presentation layer (e.g. report, web page, etc) not in SQL.

    SQL cannot do dynamically wide queries. Each column has to be defined. Therefore if you pivot you need to have a pre-determined number of columns involved, not an infinite one.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The easy answer is that in the database I would NEVER combine them.

    Things like indexes and constraints are atomic so combining them violates 1NF (First Normal Form).

    I don't have any problem with combining them as a computed column in a report or on a web page, but I'd never store them combined.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I'm sorry I should of been more clear, this is just for a query. Not creating a table based off of this. ***Tonight I can provide some data, essentially the tables are in SSMS, they are the sys.object tables, columns, indexes, schema and database. I am joining them together to tie in all the information and indexes are creating a duplicate records due to the fact some columns have more than one index or constraints. So I was going to pivot out 3 places, it seems columns in our database never have more than 3 constraints / indexes placed on them. If I could pivot out those columns I would keep my one to one and capture the indexes and constraints in one row related from database to column.

    The only problem that I can find is pivot seems to only work with aggregate functions such as sum, max, min or count.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •