SQL Single Column Table

80 pts.
Tags:
Hi - could anyone please assist and inform me of the easiest method of changing a single column table with multiple rows into a view that then displays as a single row with multiple columns

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    What database platform (and version) are you using ?
    69,510 pointsBadges:
    report
  • SAffer
    Hi Carlos The tag got missmatched somehow - SQL Server 2005.
    80 pointsBadges:
    report
  • TomLiotta
    Just to be sure, you have a table with single-column data:   Abc Defghi Jklmn   And you want a view that gives this result:   Abc Defghi Jklmn   Is that right?   Tom
    125,585 pointsBadges:
    report
  • SAffer
    Hi Tom It would most definately look like this e.g. AL1 AL2 AL3 AK01 AK02  etc. - the column being 4 alpha characters long and either 3 or 4 characters used to give AL1  AL2  AL3  AK01 AK02 AL1  AL2  BX01 BX01 etc.  I hope you can help - obviously the easiest would be a single command if there is one, otherwise a recurring structure without having to 'hard code' each column with the CASE command  
    80 pointsBadges:
    report
  • carlosdl
    The PIVOT operator is usually what you would use to create cross tab queries, but  I don't think doing it on single-column tables is its intended use. Comes to mind something like this, but you would still need to know the possible values before hand:SELECT * FROM (SELECT col1,col1 col2 FROM test1) pivot ( MAX(col1) FOR col2 IN ('AL1','AL2','AL3','AK01') ); You could alias the column names if necessary: SELECT * FROM (SELECT col1,col1 col2 FROM test1) pivot ( MAX(col1) FOR col2 IN ('AL1' AS "1",'AL2' AS "2",'AL3' AS "3",'AK01' AS "4") ); But since you are asking for a way to create a view, I suppose you expect it to automatically change as you insert new rows into the table, which is not possible with this approach You could write some code to generate this query dinamically based on the current data on the table, and put it in a trigger so the view is recreated every time a new row is inserted, but I'm not sure I would like to do something like that (I'm not sure I would have a table like that either...)
    69,510 pointsBadges:
    report
  • carlosdl
    Sorry, the editor created a mess with the formatting.
    69,510 pointsBadges:
    report
  • TomLiotta
    @carlosdl:   Couldn't the view be created over a recursive CTE? The major problem would be that it would result in a single column with all of the values in a single string. But that would be the only way to handle such a view anyway.   You couldn't do anything useful with a view that had an unknown number of columns. You couldn't do any WHERE or ORDER BY if you never knew that columns would be there. You couldn't even give the columns names.   Tom
    125,585 pointsBadges:
    report
  • carlosdl
    "You couldn’t even give the columns names." That's why I mentioned the possibility to alias the column names with numbers when creating the view (I know, you still wouldn't know the number of columns, but if you knew, you would know how to call them), but as I mentioned before, I don't think I would want to have something like that.  I think the main problem is that we don't know te reasons why this table exists in the first place, and why SAffer wants to create such a view from it.
    69,510 pointsBadges:
    report
  • SAffer
    It looks like the PIVOT command is exactly what I am looking for.   My view structure is based on what the Help for the PIVOT command suggests - a simpler way of creating summarised data rather than using a complex CASE command. I'm going to try this first - if I don't come right then I'll be back for futher assistance and explain in much more detail what I am trying to do - thanks for the feedback, I really appreciate it. 
    80 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following