Solved

SQL Select Query problems

Posted on 2016-10-11
10
48 Views
Last Modified: 2016-10-15
Hello EE,

in a table I got columns values Code, Desc01, Desc02, Desc03, Desc04 up to 15

in another table, I got columns:   Code, Qty01, Qty02, Qty03, Qty04 ... all to Qty15.

my question is, I need to read the second table but I dont want to show in headers Qty01 Qty02 ..
I want to show the real description based on the "Code value"

if code is R1
Desc01 to 15 will be let's say:   XXS, XS, S, M, L, XL, XXL, XXXL

if R2 it could be:   0 2 4 6 8 10 12 14 16

i want to show the good QTYS on the real description, without showing "Qty01" "Qty02"  you know what I mean ?

can you help on this,.. ?
0
Comment
Question by:PhilippeRenaud
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41838316
SELECT T1.*
FROM Table1 T1
    INNER JOINT Table2 T2 ON T1.Code = T2.Code

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41838323
if I do this, I will get columns name as  QTy01, Qty02 Qty03

this is not what I want
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41838324
No, it's just bringing columns from Table1 (T1).Qtys are from Table2 (T2).
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41838357
what do you want the output to show?
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41838370
If in my, lets call is TableA, I have :

Code    DEsc01   Desc02    Desc03    Desc04
R1         XXS          XS            S              M
R2          0             2               4              6


and in TableB I Got :

Code      QTY01       QTY02        QTY03        QTY04
R1           5                 0                3                  1
R2           1                 6                3                  10


if I do a query that I want to know the Qtys for R1 i woudl like the results to be like this :

XXS      XS       S       M
5          0          3      1
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41838382
if you want XXS, XS, ... to be the header, you will need to create a dynamic query.

if you want XXS, XS, ... to be the first row, this is much easier:
select DEsc01,  Desc02,    Desc03,    Desc04, ....
from tableA
where Code = 'R1'
union all
select QTY01,  QTY02,    QTY03,    QTY04, ....
from tableB
where Code = 'R1'

Open in new window




So what do you want?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41838546
@Author - You want below as column Names?
XXS      XS       S       M
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41839003
yes !
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41839619
Here !! Pls  try..

Table Creation

--

CREATE TABLE table1
(
	 Code  VARCHAR(10)
	,DEsc01  VARCHAR(10) 
	,Desc02   VARCHAR(10) 
	,Desc03   VARCHAR(10) 
	,Desc04 VARCHAR(10)
)
GO

INSERT INTO table1 VALUES
('R1'     ,    'XXS' ,         'XS',            'S',              'M'),
('R2'      ,    '0'      ,       '2'      ,         '4',              '6')


CREATE TABLE table2
(
	 Code  VARCHAR(10)
	,QTY01  INT 
	,QTY02   INT
	,QTY03   INT
	,QTY04 INT
)
GO

INSERT INTO table2 VALUES
('R1'     ,      5 ,                0,                3,                  1),
('R2'     ,      1  ,               6 ,               3 ,                 10)
GO

--

Open in new window


Solution

--


DECLARE @Code AS VARCHAR(10) = 'R1'
DECLARE @Cols AS VARCHAR(1000) = ''

SELECT @Cols = @Cols + '[' + Desc01 + '],[' + Desc02  + '],[' + Desc03 + '],[' + Desc04 + ']' FROM table1 
WHERE Code = @Code

DECLARE @Query AS VARCHAR(MAX) = ''

SET @Query = '

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY code ORDER BY(SELECT 1)) rnk FROM 
	(
		SELECT *  FROM table1			
	)p
	WHERE code = ' + '''' + @code + '''' + ' 
)
SELECT ' +  @Cols +  ' FROM 
(
	SELECT *
	FROM
	(
		SELECT * FROM CTE c 
	)t
	UNPIVOT
	(
		rnku FOR t IN ( Desc01 , Desc02  , Desc03 , Desc04 )
	) AS Unvot
)p PIVOT
(
	MAX(t) FOR rnku IN ( ' +  @Cols  + ' )
)as pvt
WHERE rnk <> 1
UNION ALL
SELECT QTY01, QTY02 , QTY03 , QTY04 FROM table2 
WHERE Code = ' + '''' + @code + ''''

EXEC (@Query)


--

Open in new window



Output for Code = R1

XXS      XS      S      M
5      0      3      1



Output for Code = R2

0      2      4      6
1      6      3      10


Hope it helps !!
0
 
LVL 1

Author Closing Comment

by:PhilippeRenaud
ID: 41845008
thanks
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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