Solved

How can I select multiple values and insert them into a single row In MS SQL

Posted on 2016-08-03
14
45 Views
Last Modified: 2016-10-08
In MS SQL I need to select particular values by name from a table and insert them into a single row in another table.

In a real basic sense I need this but 10+ results into a 10+ column row with a date/time reference.
Insert into [table2].[namealias1]
    Select [number] as namealias1 
    From [db].[table]
    Where [name] = 'name1'

Open in new window


Table1 (source)
[NAME]   [NUMBER]

Table2 (destination)
DATESTAMP  NameAlias1 NameAlias2

Or is this the best way to even think about it?
The end goal is to record the specific values with a time stamp in a single row in another table.
0
Comment
Question by:cautrey
  • 7
  • 2
  • 2
  • +2
14 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 41740632
Hi Cautrey,
Please try something similar to the following:
Insert into [table2] (DATESTAMP, [namealias1], [namealias2]
    Select 
         ,GETDATE() AS DATESTAMP
         ,[number] as namealias1
         ,'' AS namealias2
    From [db].[table]
    Where [name] = 'name1'
   UNION
Select 
         ,GETDATE() AS DATESTAMP
        ,'' AS namealias1
         ,[number] as namealias2 
    From [db].[table]
    Where [name] = 'name2'

Open in new window

0
 
LVL 13

Expert Comment

by:LIONKING
ID: 41740646
Not knowing the exact number of rows that you're dealing with makes it a lot more complicated, especially since the number of rows is what's going to drive the number of columns in your destination table (if I understood correctly).

Is this a one-time thing? Or are you trying to automate an existing ETL process of some sort?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41740713
Here's an article on how to pull that off in T-SQL using XML..
T-SQL:  Normalized data to a single comma delineated string and back
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41740733
You may need PIVOT kind of solution. Do you know how many max columns can be generated? Provide some sample data and expected output.
0
 

Author Comment

by:cautrey
ID: 41740736
It is going to be about 20 rows out of 200+ in the table.
It needs to be run every 12 hours.
We are recording particular values from a process to track changes.

I created the destination table already
0
 

Author Comment

by:cautrey
ID: 41740780
Hopefully the formatting makes sense.

I could probably pull the whole thing into a temp table. It is 200 rows and may end up 1000 max.
I need 20 or so specific values recorded from that table.

So an alias for the name field needs to be the column name and the value goes in the field.

Source Table
name   value
abc      12345
xyz       56789
def       25410

Result Table
Date                           north    south     east
08/03/2016 07:00    12345    56789    25410


Thanks everyone for any help or hints you can provide.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 41740784
Got it.  Disregard my comment about the article, as that's now not the question, as essentially you're asking for an Excel Transpose, and other experts are helping you out there.
0
 

Author Comment

by:cautrey
ID: 41740946
I am thinking of a query to create a derived table, a series of select statements against that table to assign variable names to the desired values, and then a insert into using those assigned variables.

Does this make sense?

I tried with a cte but the second select fails.
0
 

Author Comment

by:cautrey
ID: 41741371
Is there anyone available to help with this?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 41741879
Check this. Replace table/column names with your actual table and column names. Hope you will get an idea and modify as per your need.
--CREATE TABLE Source_Table (name VARCHAR(100), Value INT, Date1 DATETIME)
--INSERT Source_Table VALUES ('north',12345,'2016-08-03'),('south',56789,'2016-08-03'),('east',25410,'2016-08-03')

declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + t2.name
                           from    Source_Table as t2
                          order by '],[' + t2.name
                            for xml path('')), 1, 2, '') + ']'

declare @query nvarchar(4000)
set @query = '
select Date1,' + @cols + '
  from (select name,Value,Date1 from Source_Table) as t1
pivot (sum(Value) for name in (' + @cols + ')) as p order by Date1'
print (@query)
exec(@query)

-- Date1	east	north	south
-- 2016-08-03 00:00:00.000	25410	12345	56789

Open in new window

0
 

Author Comment

by:cautrey
ID: 41742828
Thanks Sharath!

That definitely gives me something to work with.

I don't need to declare the column names if I am just getting the values and inserting into an existing table right? If the values are queried in the correct order they can just be assigned to the destination columns in order? and for the date I just need a GETDATE in the insert right?

The problem I am running into is I have a lot of sub queries from the same column in the source.
I can UNION them into a single column but I not sure how to call them in the insert statement.
0
 

Author Comment

by:cautrey
ID: 41743102
I am still not getting it.

This is my output from my multiple select statements with UNION
value
1777151
1748018
2225472
3679721
354813
302752
1870206
1925222
1281133
2444641
53960

How do I get these values into the table? It seems like they just need to read out with commas between them and stuck into a insert-value statement.
This is the destination table:
ID | TimeStamp | NorthTower | SouthTower | Sump | Barren | St2Plant | St2Heap | St3Plant | St3Heap | St4Heap | St4Plant | SumpRec

I just need a generate unique ID and the one record of the time when inserted.
Can I assign the output of the select as variable, derived table, or an xml file insert commas and reference in the insert statement?
We will always know the number of rows because we have that number of select statements with 1 result each.

Sorry but I am a complete novice at sql and got thrown into this task .
Appreciate any help I can get.
0
 

Author Comment

by:cautrey
ID: 41743354
Got it figured out. I just brute forced it.

Since I am using individual select statements from a single column anyway I just declared variables for each one and used them in the insert.

declare @north as int set @north = (select [value] from [source table] where [name] = 'name1')
declare @south as int set @south = (select [value] from [source table] where [name] = 'name2')
declare @east as int set @east = (select [value] from [source table] where [name] = 'name3')

Insert into [dest_table] (ID,Time,north,south,east)
 Value (NEWID(),GETDATE(),@north,@south,@east)

Open in new window



Thanks everyone
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now