0

I am having very large table 151 columns - each column represents one store (150 stores * 1 column) plus one date column. 

Date         Store001       Store002      Store003      Store004 ................... Store150
---------------------------------------------------------------------------------------------------    
01/01/14     12560          8546           7468          10154                        16845

31/10/14     13978          7584           8456          13458                        25458

I need the results as follows:

Date         Store#        Amount
-----------------------------------
01/01/14     Store001      12560
01/01/14     Store002      8546
01/01/14     Store003      7468
01/01/14     Store004      10154
etc.,
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohan YGK
  • 1
  • 1

1 Answers1

0
Create table yourtable([Date]  date,Store001 int ,Store002 int)
insert into yourtable ([Date],Store001,Store002)
values
(GETDATE(),1243,546),
(GETDATE(),4545,798),
(GETDATE(),5687,456),
(GETDATE(),0756,685)

Simple Query

   ;WITH CTE
AS
  (
  SELECT * FROM (
  SELECT [Date],Store001,Store002 FROM yourtable) T
  UNPIVOT ( Value FOR N IN (Store001,Store002))P
  )
 SELECT [Date],N as Store#,SUM(Value) as Amount
 FROM CTE
 GROUP BY [Date],N

Dynamic SQL 

declare @cols nvarchar(max) 
select @cols = coalesce(@cols+N',', N'') + quotename(c.name) from syscolumns c
inner join sysobjects o on c.id = o.id and o.xtype = 'u'
where o.name = 'yourtable' and c.name not in ('Date') order by c.colid
select @cols
declare @query nvarchar(max) 

select @query = N'
 ;WITH CTE
AS
  (
  SELECT * FROM (
  SELECT [Date], ' + @cols + '
FROM yourtable) T
  UNPIVOT ( Value FOR N IN (' + @cols + '))P
  )
 SELECT [Date],N as Store#,SUM(Value) as Amount
 FROM CTE
 GROUP BY [Date],N
'
print @query 
exec sp_executesql @query

OUTPUT

Date         Store#     Amount
2014-11-02  Store001    12231
2014-11-02  Store002    2485
Dgan
  • 10,077
  • 1
  • 29
  • 51
  • Excellent Mr Ganesh. I just registered and never expected such quick response. – Mohan YGK Nov 02 '14 at 14:14
  • @MohanYGK Your welcome if u think this is helpful then mark it as answer – Dgan Nov 02 '14 at 14:15
  • We are implementing AX 2012 for 150 retail outlets covering production, inventory, retail management, payroll, treasury etc., I am looking for a IT manager position in my company. I don't know your background and your interests, but if you are interested, let me know. – Mohan YGK Nov 02 '14 at 14:16
  • Going back to the query, as an example, I mentioned as store001, store002. But what we are doing is based on city name, we are giving the store code. For example, in Riyadh, we are defining store code as RYD01, RYD02.... but if in Jeddah, we are defining JED01, JED02 etc., we are having our presence in 27 stores. In such case, dynamic query may not work. Any other way to address this issue. – Mohan YGK Nov 02 '14 at 14:18
  • @MohanYGK Please Edit Your question with your sample data and what you want to say – Dgan Nov 02 '14 at 14:24
  • @Dyanmic SQL only for getting desire columns names from `syscolumns` all system columns – Dgan Nov 02 '14 at 14:25
  • @MohanYGK I think you need to normalize your Table. Don't store values like this `RYD01, RYD02`....etc.... – Dgan Nov 02 '14 at 14:40
  • @MohanYGK Then its time change or Migrate (Totally your decision) – Dgan Nov 02 '14 at 15:21
  • are you interested to join our Group in KSA (Riyadh) as IT Manager – Mohan YGK Nov 02 '14 at 15:49
  • We are implementing AX 2012 – Mohan YGK Nov 02 '14 at 15:49
  • @MohanYGK Thanks for Your appreciation.I would like to work.But I am from Mumbai.Your company is in Saudi Arabia :O – Dgan Nov 02 '14 at 16:04
  • We will provide you the employment contract and you can relocate to Riyadh. – Mohan YGK Nov 03 '14 at 09:17
  • @MohanYGK Can I work Part time for your company from India ?? – Dgan Nov 03 '14 at 18:28
  • No boss. It is full time job. We are implementing AX 2012 for massive operations. If any of your friends looking for the abroad opportunity as IT manager, please let me know. – Mohan YGK Nov 04 '14 at 07:39
  • Yes Sir, if there is any one i will let you know – Dgan Nov 04 '14 at 11:12
  • I would like to upload data from excel to sql server using VB code. Do you have any code – Mohan YGK Jan 29 '15 at 18:45
  • @MohanYGK vb.net or vb 6.0 ?? – Dgan Jan 30 '15 at 05:08
  • any thing is OK. I am interested in the results – Mohan YGK Jan 31 '15 at 06:57
  • Regarding uploading data from excel to SQL server by using Excel VB – Mohan YGK Feb 26 '15 at 06:25
  • http://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/ check this it will convert your excel sheet into table – Dgan Feb 26 '15 at 06:58
  • Sir Ji, this will not insert additional rows into SQL table. In other words, I need to insert the rows into SQL table – Mohan YGK Feb 26 '15 at 07:02
  • @MohanYGK mail your excel to me gdevlekar[at]gmail[dot]com – Dgan Feb 26 '15 at 07:50
  • Boss check your email – Mohan YGK Feb 26 '15 at 08:06